demo of using large objects interface in php

From: danny <danny_ml1(at)sh(dot)fishinteractive(dot)com>
To: "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: demo of using large objects interface in php
Date: 2000-09-15 02:57:08
Message-ID: 200009150309.LAA07067@sh.fishinteractive.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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(at)fishinteractive(dot)com

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Teter 2000-09-15 08:47:22 get table schema in psql?
Previous Message Stephan Szabo 2000-09-14 19:39:36 Re: left and outer joins?