Re: [SQL] Help creating rules/triggers/functions
Blaise Carrupt wrote: > Hi all ! > > I use PostgreSQL 7.0.2 on a HP-UX system. > > I would like to create a simple function and a simple trigger (or rule) that > deny a delete from a table if the row is referenced in another table. > > I though it should look like this (from my Ingres experience... :) : > > create function A_del(int4 i_id) > BEGIN >SELECT id > FROM b > where a_id = :i_id; > >if rowcount > 0 then >RAISE EXCEPTION "not allowed !" >end if; > END > CREATE FUNCTION A_del () RETURNS opaque AS ' DECLARE nrefs integer; BEGIN nrefs := count(*) FROM b WHERE a_id = OLD.i_id; IF nrefs > 0 THEN RAISE EXCEPTION ''a_id % still referenced from b'', OLD.i_id; END IF; RETURN OLD; END;' LANGUAGE 'plpgsql'; > > create trigger before delete from A for each row execute procedure A_del(old.id) CREATE TRIGGER A_del BEFORE DELETE ON A FOR EACH ROW EXECUTE PROCEDURE A_del(); > > > But it seems to be much more complicated with Postgres (create a C function > using CurrentTriggerData,...). May I have missed something or is it really much > more complicated ? Alternatively (IMHO preferred) you could use a referential integrity constraint in table B, which would also cover UPDATE on A and check values inserted/updated into/in B. CREATE TABLE B ( ... FOREIGN KEY (i_id) REFERENCES A (a_id) Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] lo_import for storing Blobs
You can use 'DBI'
from test.pl of DBD::Pg
# create large object from binary file
my ($ascii, $pgin);
foreach $ascii (0..255) {
$pgin .= chr($ascii);
};
my $PGIN = '/tmp/pgin';
open(PGIN, ">$PGIN") or die "can not open $PGIN";
print PGIN $pgin;
close PGIN;
# begin transaction
$dbh->{AutoCommit} = 0;
my $lobjId;
( $lobjId = $dbh->func($PGIN, 'lo_import') )
and print "\$dbh->func(lo_import) .. ok\n"
or print "\$dbh->func(lo_import) .. not ok\n";
# end transaction
$dbh->{AutoCommit} = 1;
unlink $PGIN;
or you can use 'Perl5 extension for PostgreSQL' ...
note: i didn't test the script
use strict;
use Pg;
my $dbname = 'your dbname';
my $lo_path = 'path/to/you/binaryfile';
my ($tbl, $fld) = ('your table', 'oid field');
my $conn = Pg::connectdb("dbname=$dbname");
die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status;
my $result = $conn->exec("BEGIN");
die $conn->errorMessage unless PGRES_COMMAND_OK eq $result->resultStatus;
# import large object and get its oid
my $new_oid = $conn->lo_import($lo_path) or die $conn->errorMessage;
$result = $conn->exec("END");
die $conn->errorMessage unless PGRES_COMMAND_OK eq
$result->resultStatus;
# insert the oid of the lobj
my $sql = sprintf("INSERT INTO %s (%s) VALUES (%ld)",
$tbl, $fld, $new_oid);
$result = $conn->exec($sql);
die $conn->errorMessage unless PGRES_COMMAND_OK eq
$result->resultStatus;
undef $conn;
Sherwin
[EMAIL PROTECTED] writes:
>I need to store a binary file in a database. I use a cgi writed in shell
>to do it. So I can use Postgres user to execute the cgi.
>
>How can I store a binary file in a database with a cgi ?
>
>Thanks a lot.
>
>Laurent.
>
>
>
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [SQL] Insert into VIEW ???
Jacek Zagorski wrote: > > Is it possible to INSERT into xyz > where xyz is a view ? > What is the proper syntax ? > > Thanks Much > Jacek Zagorski You'll need to set up the rules for updating - PG can't figure out what you want automatically. There's a page on this in the programmer's guide. - Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Temp Tables & Connection Pooling
David Olbersen wrote: > > On Fri, 2 Mar 2001, Gerald Gutierrez wrote: > > ->Recently I wanted to implement Dijkstra's algorithm as a stored procedure, > ->and finding that PL/PGSQL cannot return record sets, I thought about using > ->a temporary table for the results. If tempoary tables are session-specific, > ->however, then wouldn't connection pooling make it unusable since the table > ->might "disappear" from one query to the next? What are alternative > ->approaches to implementing Dijkstra's algorithm inside the database? > > > Wouldn't a VIEW do what you want? > > > -- Dave Presumably Gerald's after speed here - IIRC Dijkstra's is shortest path finder, so probably not cheap. I was thinking about the temp table problem the other day, and the best I could come up with involved creating a higher-level connection (application-level session basically). You'd create a table mytempNNN (where NNN is a unique number to identify your user's session) and add a line to a tracking table (NNN,now()) Every time you use mytempNNN update the tracking table's time and run a separate reaper process to kill anything not used for 15 minutes (or whatever). You should be able to automate this to a degree with triggers etc. - Richard Huxton ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Temp Tables & Connection Pooling
At 12:48 PM 3/2/2001 -0800, David Olbersen wrote: >On Fri, 2 Mar 2001, Gerald Gutierrez wrote: > >->Recently I wanted to implement Dijkstra's algorithm as a stored procedure, >->and finding that PL/PGSQL cannot return record sets, I thought about using >->a temporary table for the results. If tempoary tables are session-specific, >->however, then wouldn't connection pooling make it unusable since the table >->might "disappear" from one query to the next? What are alternative >->approaches to implementing Dijkstra's algorithm inside the database? > > >Wouldn't a VIEW do what you want? > No it wouldn't. Executing Dijkstra would involve executing iterative logic on multiple tables and storing intermediate results in a form that can be returned to the user but does not affect the actual persistent table schema (e.g. a record set, or a temporary table). A view is used to provide a simplified or alternative way of looking at a set of data, and cannot cannot generally multi-step operation that data prior to returning to the user. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB?
- Original Message - From: The Hermit Hacker <[EMAIL PROTECTED]> To: Jaruwan Laongmal <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Friday, March 02, 2001 8:04 PM Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in DB? > On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: > > > I had deleted a very large number of records out of my SQL table in order to > > decrease the harddisk space. But after I use command 'ls -l > > /usr/local/pgsql/data/base/', it is found that the size of concerning files > > do not reduce due to the effect of 'delete' SQL command. What should I do > > if I would like to decrease the harddisk space? > > VACUUM > > could anyone remove this nasty bug in 7.2? this is already a big pain and is the reason why am I still using MySQL in my product server. another nasty thing is it does not allow me to reference table in another database. sigh. Regards, XuYifeng ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] why the DB file size does not reduce when 'delete'thedata in DB?
On Sun, 4 Mar 2001, xuyifeng wrote: > > - Original Message - > From: The Hermit Hacker <[EMAIL PROTECTED]> > To: Jaruwan Laongmal <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> > Sent: Friday, March 02, 2001 8:04 PM > Subject: Re: [HACKERS] why the DB file size does not reduce when 'delete'the data in >DB? > > > > On Fri, 2 Mar 2001, Jaruwan Laongmal wrote: > > > > > I had deleted a very large number of records out of my SQL table in order to > > > decrease the harddisk space. But after I use command 'ls -l > > > /usr/local/pgsql/data/base/', it is found that the size of concerning files > > > do not reduce due to the effect of 'delete' SQL command. What should I do > > > if I would like to decrease the harddisk space? > > > > VACUUM > > > > > > could anyone remove this nasty bug in 7.2? this is already a big pain > and is the reason why am I still using MySQL in my product server. > another nasty thing is it does not allow me to reference table in > another database. sigh. Its actually not considered a *bug*, but it was a feature that was part of an older feature that was removed. Vadim has plans for implementing an OverWriting Storage Manager, but scheduale of it is uncertain ... could be for v7.2 ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Passing table names to PL/PGSQL for SELECT/UPDATE/INSERT
I've written my Dijkstra's algorithm in PL/PGSQL. It didn't turn out to be a big deal at all actually, programming-wise. I understand execution speed will be poor but it shouldn't be any slower than having something else, like PHP or Java, execute logic and query the database. I'd like to generalize my function. As per Richard Huxton's suggestion to create tables named after session ID (thanks Richard)s, I'd like to pass in some table names so that the algorithm can read from and write into tables that I specify as parameters to the function. Sometihng like: select dijkstra(inputtablename, outputtablename); I've tried typing the parameters as TEXT, and then just inserting $1 in the select (e.g. SELECT * from $1 ...) This doesn't work, nor did a number of other ways I tried. How can this be done? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL]
I don't seem to be able to create tables (persistent or temporary) from within a PL/PGSQL function. With the following script, I can create the function fine: CREATE FUNCTION tst() RETURNS INTEGER AS ' BEGIN CREATE TABLE ttt(a int); RETURN 0; END; ' LANGUAGE 'plpgsql'; ... but when I execute it , I get the following error. The error happens regardless of whether I use CREATE TABLE or CREATE TEMPORARY TABLE: t1=> \i tst.sql DROP CREATE t1=> select tst(); ERROR: copyObject: don't know how to copy 611 Is it somehow wrong to create tables from within a PL/PGSQL function? Incidently, is it possible to generate a guaranteed unique table name while creating tables, much like some UNICES' ability to generate guaranteed unique file names for tempoary files? Thanks. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
