Thread: assigning result of SELECT in TRIGGER
Upon inserting a record into the confirm table I get this error in psql:
ERROR: parser: parse error at or near "SELECT"
What am I doing wrong. Is there a proper way to get a value from a related
table.
The function and trigger defs are:
CREATE FUNCTION chk_status () RETURNS OPAQUE AS '
DECLARE
status BOOLEAN;
BEGIN
status := SELECT status FROM log WHERE log.log_no = NEW.log_no;
IF NOT status THEN
RAISE EXCEPTION ''The log entry you are trying to confirm
was not successfully forwarded and does not need confirmation'';
END IF;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER chk_status BEFORE INSERT ON confirm FOR EACH ROW EXECUTE
PROCEDURE chk_status();
--
Randy Perry
sysTame
Mac Consulting/Sales
phn 561.589.6449
mobile email help@systame.com
Hello,
I´ve got the Win-ODBC driver for postgres from
ftp://ftp.de.postgresql.org/pub/odbc/versions/dll/psqlodbc-07_01_0005.zip
It works well with Postgres 7.1. I just have the problem that the
information about primary indexes is not transmitted to Access if I
want to do a connection to external tables. I checked the box
named "Recognize Unique Indexes" in the drivers "Advanced Options"
dialog but Access seems to take no notice and every time asks me
which fields can be used a unique index. That´s quite boring and
I wonder if there are any other fields to change to get the information
right.
System Information:
Server: Debian GNU/Linux with PostgreSQL 7.1.2
Client: Win NT, Access 97 (if you need more detailed information about
this box please ask - I´m very seldom on those kind of machines
and don´t know what could be relevant)
Kind regards
Andreas.
PS: Is this the right list for those kind of questions.
Plpgsql is getting confused by the double usage of the name "status", i.e. as a variable, and as the name of a field. Choosing a different name for your variable should work. I think the usual syntax for this kind of operation is: SELECT field INTO variable FROM.... I don't see why your construction wouldn't work, though. Anyone want to comment on whether this makes a difference, and why? ----- Original Message ----- From: "Randall Perry" <rgp@systame.com> To: <pgsql-general@postgresql.org> Sent: Thursday, August 16, 2001 9:01 PM Subject: [GENERAL] assigning result of SELECT in TRIGGER > Upon inserting a record into the confirm table I get this error in psql: > > ERROR: parser: parse error at or near "SELECT" > > What am I doing wrong. Is there a proper way to get a value from a related > table. > > > > The function and trigger defs are: > > CREATE FUNCTION chk_status () RETURNS OPAQUE AS ' > DECLARE > status BOOLEAN; > BEGIN > status := SELECT status FROM log WHERE log.log_no = NEW.log_no; > IF NOT status THEN > RAISE EXCEPTION ''The log entry you are trying to confirm > was not successfully forwarded and does not need confirmation''; > END IF; > END; > ' LANGUAGE 'plpgsql'; > > > CREATE TRIGGER chk_status BEFORE INSERT ON confirm FOR EACH ROW EXECUTE > PROCEDURE chk_status(); > > -- > Randy Perry > sysTame > Mac Consulting/Sales > > phn 561.589.6449 > mobile email help@systame.com > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >
"Jeff Eckermann" <jeckermann@verio.net> writes:
> Anyone want to comment on whether this makes a difference, and why?
>> CREATE FUNCTION chk_status () RETURNS OPAQUE AS '
>> DECLARE
>> status BOOLEAN;
>> BEGIN
>> status := SELECT status FROM log WHERE log.log_no = NEW.log_no;
You can write the assignment as either
status := status FROM log WHERE log.log_no = NEW.log_no;
(*no* SELECT keyword here) or
SELECT INTO status status FROM log WHERE log.log_no = NEW.log_no;
(there's actually a good deal of freedom about where to put the INTO,
but I tend to like putting it first).
As Jeff points out, this isn't going to work as-is even when you get
past the simple syntactic error, because plpgsql is going to think that
all instances of "status" refer to its variable, not to fields of some
table. Possible ways to handle this include:
1. Rename the plpgsql variable to not conflict with any fields you
want to access. This is probably the least confusing way.
2. Explicitly qualify the conflicting field names. For example
status := log.status FROM log WHERE log.log_no = NEW.log_no;
would work.
3. Double-quote the field names. I haven't tried this, but from looking
at the plpgsql sources I think this would work:
status := "status" FROM log WHERE log.log_no = NEW.log_no;
regards, tom lane
Greetings: I'm having problems stopping the postmaster. As I'm very well aware, kill -i on the postmaster is a Bad Thing (tm), so what are my options? What can I look for to help me narrow down the problem? Any help appreciated. Below is a log from what I'm doing. Thanks! Chris [root@gumby bin]# ./pg_ctl -D /var/lib/pgsql/data/ stop waiting for postmaster to shut down................................................................ failed pg_ctl: postmaster does not shut down You have mail in /var/spool/mail/cmulcahy [root@gumby bin]# ./pg_ctl -D /var/lib/pgsql/data/ stop waiting for postmaster to shut down................................................................ failed pg_ctl: postmaster does not shut down
I have a similar setup as you (PostgreSQL 7.1.2, MS Access 97 and psqlodbc). However, I use Win98, not NT. I changed the odbc configuration as follows: Advanced Options (Driver) Select "Recognize Unique Indexes" Select "Use Declare/Fetch" Deselect "Read Only" Advanced Options (Data Source) Deselect "Read Only" I _strongly_ advise that you select "Use Declare/Fetch". It will prevent some queries from timing out due to memory limitations. Primary keys are recognized on my system automatically. Caveats: 1. Primary keys are lost when exporting an Access table to PostgreSQL. You'll have to create unique indexes. 2. If you drop your indexes to speed up copying, remember to recreate them. I hope this helps, Andrew Gould --- "Tille, Andreas" <TilleA@rki.de> wrote: > Hello, > > I�ve got the Win-ODBC driver for postgres from > > > ftp://ftp.de.postgresql.org/pub/odbc/versions/dll/psqlodbc-07_01_0005.zip > > It works well with Postgres 7.1. I just have the > problem that the > information about primary indexes is not transmitted > to Access if I > want to do a connection to external tables. I > checked the box > named "Recognize Unique Indexes" in the drivers > "Advanced Options" > dialog but Access seems to take no notice and every > time asks me > which fields can be used a unique index. That�s > quite boring and > I wonder if there are any other fields to change to > get the information > right. > > System Information: > Server: Debian GNU/Linux with PostgreSQL 7.1.2 > Client: Win NT, Access 97 (if you need more > detailed information about > this box please ask - I�m very seldom on > those kind of machines > and don�t know what could be relevant) > > Kind regards > > Andreas. > > PS: Is this the right list for those kind of > questions. > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the > unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) __________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
Chris Mulcahy writes: > I'm having problems stopping the postmaster. As I'm very well aware, > kill -i on the postmaster is a Bad Thing (tm), so what are my options? > What can I look for to help me narrow down the problem? pg_ctl stop -m fast or make your clients disconnect. > > Any help appreciated. Below is a log from what I'm doing. > > Thanks! > Chris > > [root@gumby bin]# ./pg_ctl -D /var/lib/pgsql/data/ stop > waiting for postmaster to shut > down................................................................ > failed > pg_ctl: postmaster does not shut down > You have mail in /var/spool/mail/cmulcahy > [root@gumby bin]# ./pg_ctl -D /var/lib/pgsql/data/ stop > waiting for postmaster to shut > down................................................................ > failed > pg_ctl: postmaster does not shut down > > > -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter