[SQL] Database joins
Hi, I have created two databases, db1 and db2 in my POSTGRESQL database system. And both the databases contains few tables also. Could you please help me to write a query which should retrieve data from both the databases (Database joins and table joins) Expecting and earliest reply Regards Jay
[SQL] triggering an external action
I am trying to find the best way for a database trigger to signal a client process to take an action. Specifically, I am working on the classic problem of creating and modifying system accounts based on the updates to a "person registry" database. The basic model I'm working with has triggers on my tables of interest that stick a person's unique ID into a "todo queue" table whenever modifications are made. The queue is periodically polled by a script which processes and deletes each "todo" record. The polling script goes to sleep for gradually increasing periods of time whenever it polls the queue and finds it empty. What I want is a trigger on the "todo" table that will "kick" my processing script to make it wake up and process the queue immediately. In an Oracle environment, I think I could use a database pipe to achieve more or less the behavior I'm looking for, but I can't find anything that does what I want in pgsql at the database level. I could write a trigger in C or Perl or something that would do something at the OS level, like send a signal, but when I go down that path I start having to deal with unix issues like having to elevate to root privs to send a signal to a process that isn't running as the pgsql user, etc. It is doable but gets complex quickly. Is there anything I'm missing at the database level that would help me process updates in realtime? (And if not, which of the other mailing lists would be the most appropriate place for me to discuss the best way to implement an OS-level solution?) Thanks, -jbp -- Jay Parker - UALR Computing Services - Networks Project Manager [EMAIL PROTECTED] - http://www.ualr.edu/jbparker - 501-569-3345 But I have promises to keep, And miles to go before I sleep. -Frost ---(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] triggering an external action
On 05/17/2005 01:07 PM, Bricklen Anderson wrote: How about LISTEN and NOTIFY, would they work for this? Yes, that is precisely what I need... and what I have somehow overlooked during at least a dozen passes through the docs. Sigh. Thanks for your help, -jbp -- Jay Parker - UALR Computing Services - Networks Project Manager [EMAIL PROTECTED] - http://www.ualr.edu/jbparker - 501-569-3345 But I have promises to keep, And miles to go before I sleep. -Frost ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] access to new/old in dynamic sql for audit table
I am revisiting the age-old audit table issue, and am curious to see
whether I can get away with not writing custom trigger functions for
every table being audited.
My design has a foo_audit schema for each schema foo containing tables
to be audited, so triggers on foo.bar will insert rows into
foo_audit.bar, which is a clone of foo.bar with some extra columns added.
I would like to have three generic functions for insert/update/delete,
instead of three custom functions for each audited table. The problem,
of course, is handling the different column structures. I suppose I
could go look things up in the catalog and generate dynamic sql based on
the tables structure recorded there, but that seems like way too much
overhead for an audit system.
I tried something like this, but it didn't like the reference to "new"
inside the execute string:
CREATE OR REPLACE FUNCTION
meta.audit_insert_trig() RETURNS trigger SECURITY DEFINER
AS $PROC$
DECLARE
varschema TEXT;
BEGIN
varschema = tg_argv[0];
if varschema is null or char_length(varschema) = 0 then
raise exception 'must create trigger with schema name as arg';
end if;
new.auditrowid := nextval('meta.auditrowid_seq');
execute 'insert into '
|| quote_ident(varschema||'_audit.'||tg_relname)
|| ' select now(),NULL,''I'',new.*';
return new;
END
$PROC$ LANGUAGE plpgsql;
The error I get is: "ERROR: NEW used in query that is not in a rule",
which doesn't seem like an unreasonable limitation. Is there any way to
accomplish this (efficiently) in a generic function, or should I go back
to my previous implementation with dozens of custom trigger functions?
While I've got your attention, I'll toss in some related questions:
- What is the efficiency tradeoff of having a single combined function
with a conditional to detect insert/update/delete, versus having three
specific functions that duplicate some common code but don't have the
conditional?
- Is there an easier way to get the name of the schema associated with a
table from inside a trigger, rather than the trigger argument kludge I
used above?
Thanks,
-jbp
--
Jay Parker - UALR Computing Services - Networks Project Manager
[EMAIL PROTECTED] - http://www.ualr.edu/jbparker - 501-569-3345
But I have promises to keep, And miles to go before I sleep. -Frost
---(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
[SQL] Question about SQL Control Structure(if then, for loop)
Hello, I am new to PostgreSQL and want to use the IF-THEN; LOOP statements in my SQL command file. Thus I may use psql -f cfile to exceute. However I always got syntax error on if/for in psql. For example: RDM=# for i in 1 .. 10 loop RDM-# select "test" RDM-# end loop; ERROR: syntax error at or near "for" at character 1 LINE 1: for i in 1 .. 10 loop RDM=# if exits ( select * from testtable) RDM-# then RDM-# select "TEST" RDM-# ; ERROR: syntax error at or near "if" at character 1 LINE 1: if exits ( select * from testtable) ^ Can someone tell me how to use if/for in psql? The document shows the control structures in the PL/pgSQL section. Must I use if/for inside some procedure/function? Thanks a lot. Jay Get your own "800" number Voicemail, fax, email, and a lot more http://www.ureach.com/reg/tag ---(end of broadcast)--- TIP 6: explain analyze is your friend
