Re: [GENERAL] Syntax error in trigger
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 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 <
> akretsch...@spamfence.net> wrote:
>
>> Jignesh Shah 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: [GENERAL] Syntax error in trigger
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 <
akretsch...@spamfence.net> wrote:
> Jignesh Shah 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: [GENERAL] Syntax error in trigger
Jignesh Shah 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
[GENERAL] Syntax error in trigger
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
