Thread: Syntax error in trigger

Syntax error in trigger

From

Jignesh Shah

Date:

21 April 2010, 15:09:09

Hi All,

I have written below trigger and applied on the table but it is giving syntax error when it gets invoked. Could you please help me what is wrong? I have given complete details here:

my trigger function:

CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
RETURNS trigger AS
$BODY$

my $id    = $_TD->{new}{'id'};
my $query = "
   INSERT INTO log_table(id)
   SELECT $1
   EXCEPT SELECT id   --> throwing error for here: syntax error at or near "EXCEPT"
   FROM loggingtable
   WHERE id = $1;";

if (exists($_SHARED{$query})) {
$plan = $_SHARED{$query};
} else {
$plan = spi_prepare($query, 'INTEGER');
$_SHARED{$query} = $plan;
}

spi_exec_prepared($plan, '$id');

$BODY$
LANGUAGE 'plperl' VOLATILE SECURITY DEFINER

Tables and trigger definitions:

CREATE TABLE techdb_table( id integer NOT NULL, info varchar NOT NULL );

CREATE TABLE log_table( id integer NOT NULL, txid integer NOT NULL DEFAULT txid_current(), txtime timestamp NOT NULL DEFAULT transaction_timestamp());

CREATE TRIGGER techdb_trigger
BEFORE INSERT ON techdb_table
FOR EACH ROW EXECUTE PROCEDURE techdb_logtable_trigger();

Error message:

Executing "insert into techdb_table values(1, 'test');" command gives below error.

ERROR: error from Perl function "techdb_logtable_trigger": syntax error at or near "EXCEPT" at line 15.

********** Error **********

ERROR: error from Perl function "techdb_logtable_trigger": syntax error at or near "EXCEPT" at line 15.
SQL state: XX000

Thanks,

Jignesh

Re: Syntax error in trigger

From

Andreas Kretschmer

Date:

21 April 2010, 15:34:34

Jignesh Shah <jignesh.shah1980@gmail.com> wrote:

> Hi All,
>
> I have written below trigger and applied on the table but it is giving syntax
> error when it gets invoked. Could you please help me what is wrong? I have
> given complete details here:
>
> my trigger function:
>
> CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
>   RETURNS trigger AS
> $BODY$
> my $id    = $_TD->{new}{'id'};
> my $query = "
>    INSERT INTO log_table(id)
>    SELECT $1
>    EXCEPT SELECT id   --> throwing error for here: syntax error at or near

Select from which table? I think, there is the table-name missing,
right?


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Syntax error in trigger

From

Jignesh Shah

Date:

22 April 2010, 08:32:53

Thanks Anreas but I think that's not a issue. The issue is something to do with "$1". I don't know how to get rid of it.

techdb=# INSERT INTO log_table(id) SELECT 5 EXCEPT SELECT id FROM log_table WHERE id = 5;
INSERT 0 1
techdb=# select * from log_table;
id | txid | txtime
----+--------+----------------------------
5 | 196552 | 2010-04-22 09:30:10.509326
(1 row)

techdb=#

Could anyone please help me out here.

Thanks,

Jignesh

On Wed, Apr 21, 2010 at 9:04 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:

Jignesh Shah <jignesh.shah1980@gmail.com> wrote:

> Hi All,
>
> I have written below trigger and applied on the table but it is giving syntax
> error when it gets invoked. Could you please help me what is wrong? I have
> given complete details here:
>
> my trigger function:
>
> CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
> RETURNS trigger AS
> $BODY$
> my $id = $_TD->{new}{'id'};
> my $query = "
> INSERT INTO log_table(id)
> SELECT $1
> EXCEPT SELECT id --> throwing error for here: syntax error at or near

Select from which table? I think, there is the table-name missing,
right?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: Syntax error in trigger

From

Jignesh Shah

Date:

22 April 2010, 08:46:21

I got it resolved. I just need to put below statement into single quote instead of double quote. :)

my $query ='INSERT INTO log_table(id) SELECT $1 EXCEPT SELECT id FROM log_table WHERE id = $1;';

Thanks,

Jignesh

On Thu, Apr 22, 2010 at 2:02 PM, Jignesh Shah <jignesh.shah1980@gmail.com> wrote:

Thanks Anreas but I think that's not a issue. The issue is something to do with "$1". I don't know how to get rid of it.

techdb=# INSERT INTO log_table(id) SELECT 5 EXCEPT SELECT id FROM log_table WHERE id = 5;
INSERT 0 1
techdb=# select * from log_table;
id | txid | txtime
----+--------+----------------------------
5 | 196552 | 2010-04-22 09:30:10.509326
(1 row)
techdb=#

Could anyone please help me out here.

Thanks,
Jignesh
On Wed, Apr 21, 2010 at 9:04 PM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
Jignesh Shah <jignesh.shah1980@gmail.com> wrote:

> Hi All,
>
> I have written below trigger and applied on the table but it is giving syntax
> error when it gets invoked. Could you please help me what is wrong? I have
> given complete details here:
>
> my trigger function:
>
> CREATE OR REPLACE FUNCTION techdb_logtable_trigger()
> RETURNS trigger AS
> $BODY$
> my $id = $_TD->{new}{'id'};
> my $query = "
> INSERT INTO log_table(id)
> SELECT $1
> EXCEPT SELECT id --> throwing error for here: syntax error at or near

Select from which table? I think, there is the table-name missing,
right?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general