pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: danny <danny_ml1@sh.fishinteractive.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: demo of using large objects interface in php
Date: Fri, 15 Sep 2000 10:57:8 +0800
Message-ID: <200009150309.LAA07067@sh.fishinteractive.com> (raw)

Hi,

Finally, I can give the demo of using large objects interface in php.

the test table:

create table lo_test (
	id serial,
	raster oid,
	primary key(id)
);


file: lo_insert.php
<html>
<?php

$db = pg_connect("dbname=template1 user=postgres");
if (!$db) die;
pg_exec($db, "begin");

$oid=pg_locreate($db);
echo "oid=$oid<br>\n";
$handle = pg_loopen ($db, $oid, "w");
echo ("handle=$handle<br>\n");
$fp = fopen ("/etc/rc.d/rc.local", "r");
echo "$fp";
while (!feof($fp)) {
	pg_lowrite($handle, fread($fp, 4096));
}
fclose($fp);
pg_loclose($handle);
pg_exec($db, "insert into lo_test (raster) values ($oid)");
pg_exec($db, "commit");
pg_close($db);
?>
</html>

file: lo_read.php
<html>
<?php
$db = pg_connect("dbname=template1 user=postgres");
if (!$db) die;
pg_exec($db, "begin");
pg_exec($db, 'select raster from lo_test order by raster desc');
$record = @pg_fetch_array($db, 1);
$oid=$record[0];
echo "$oid<br>\n";
$handle = pg_loopen ($db, $oid, "r");
echo ("$handle\n");
//pg_loreadall($handle);
while ($buf=pg_loread($handle, 80)) {
	echo nl2br($buf);
}

pg_loclose($handle);
pg_exec($db, "commit");
pg_close($db);
?>
</html>

file: lo_delete.php
<html>
<?php
$db = pg_connect("dbname=template1 user=postgres");
if (!$db) die;
pg_exec($db, "begin");
pg_exec($db, 'select id, raster from lo_test order by raster desc');
$record = @pg_fetch_array($db, 1);
$oid=$record[1];
echo "$oid<br>\n";
pg_lounlink ($oid);
pg_exec($db, sprintf('delete from lo_test where id = %s', $record[0]));
pg_exec($db, "commit");
pg_close($db);
?>
</html>


There is a better way to delete the large object associated with the record.

create trigger like this:

CREATE FUNCTION lo_test_del () RETURNS OPAQUE AS '
BEGIN
	perform lo_unlink(OLD.raster);
	RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER "tg_lo_test_del" BEFORE DELETE ON "lo_test"  FOR EACH ROW EXECUTE PROCEDURE "lo_test_del" ();

when you run
'delete from lo_test where id = 1'
the large object will be deleted in cascade.


regards
Danny Chen

dannychen@fishinteractive.com





Message-ID: <200009150309.LAA07067@sh.fishinteractive.com>
Permalink:  ../200009150309.LAA07067@sh.fishinteractive.com/
Also on:    postgresql.org/message-id/200009150309.LAA07067@sh.fishinteractive.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: danny_ml1@sh.fishinteractive.com
  Subject: Re: demo of using large objects interface in php
  In-Reply-To: <200009150309.LAA07067@sh.fishinteractive.com>
  References: <200009150309.LAA07067@sh.fishinteractive.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox