pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Pl Pgsql problem
5+ messages / 2 participants
[nested] [flat]
* Pl Pgsql problem
@ 2003-04-28 09:15 lihao <lihao@sageway.com.sg>
2003-04-28 08:54 ` Re: Pl Pgsql problem Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 5+ messages in thread
From: lihao @ 2003-04-28 09:15 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am using 7.1.3 on RedHat Linux 7.2.
I try to create a trigger on the table, I wrote a function in plpgsql,
which always fail on this sql command:
select (''UPD'' || int2hex(nextval(''updid''), 8)) as v_updid;
The function is like this:
begin
select (''UPD'' || int2hex(nextval(''updid''), 8)) as
v_updid;
if not found then
raise exception ''Unable to get updid.'';
end if;
end;
It give me this error when I trigger the function:
NOTICE: Error occurred while executing PL/pgSQL function
pg_fct_bf_ins_inf_article
NOTICE: line 9 at SQL statement -- the SQL select command
ERROR: parser: parse error at or near "$1"
I can execute the SQL without problem.
<<
select ('UPD' || int2hex(nextval('updid'), 8)) as v_updid;
>>
Can anyone help me to see what is the problem here.
Li Hao
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pl Pgsql problem
2003-04-28 09:15 Pl Pgsql problem lihao <lihao@sageway.com.sg>
@ 2003-04-28 08:54 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-28 09:29 ` Re: Pl Pgsql problem lihao <lihao@sageway.com.sg>
0 siblings, 1 reply; 5+ messages in thread
From: Tomasz Myrta @ 2003-04-28 08:54 UTC (permalink / raw)
To: lihao <lihao@sageway.com.sg>; +Cc: pgsql-sql
Dnia 2003-04-28 11:15, Uz.ytkownik lihao napisa?:
> ERROR: parser: parse error at or near "$1"
This error is often caused by variables defined with the same names as
table columns.
Anyway it would be easier for us if you write source of line which
caused error.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pl Pgsql problem
2003-04-28 09:15 Pl Pgsql problem lihao <lihao@sageway.com.sg>
2003-04-28 08:54 ` Re: Pl Pgsql problem Tomasz Myrta <jasiek@klaster.net>
@ 2003-04-28 09:29 ` lihao <lihao@sageway.com.sg>
2003-04-28 09:20 ` Re: Pl Pgsql problem Tomasz Myrta <jasiek@klaster.net>
0 siblings, 1 reply; 5+ messages in thread
From: lihao @ 2003-04-28 09:29 UTC (permalink / raw)
To: 'Tomasz Myrta' <jasiek@klaster.net>; +Cc: pgsql-sql
Hi,
Here is the source:
CREATE OR REPLACE FUNCTION pg_fct_bf_ins_inf_article () RETURNS OPAQUE
AS '
DECLARE
v_language sys_codetable.id%type;
v_updtype sys_codetable.id%type;
v_updid varchar(12);
v_articleurl varchar(60);
Begin
begin
select (''UPD'' || int2hex(nextval(''updid''), 8)) as
v_updid;
if not found then
raise exception ''Unable to get updid.'';
end if;
end;
begin
select coalesce(id,1)
into v_language
from sys_codetable
where valueen = ''ENGLISH'' and fieldname = ''LANGUAGE'' ;
if not found then
raise exception ''Unable to get language.'';
end if;
end;
begin
select coalesce(id,1)
into v_updtype
from sys_codetable
where valueen = ''ARTICLE'' and fieldname = ''UPDTYPE'' ;
if not found then
raise exception ''Unable to get updtype.'';
end if;
end;
begin
select value into v_articleurl
from sys_parameter
where key = ''ARTICLEURL'';
if not found then
raise exception ''Unable to get articleurl.'';
end if;
end;
begin
insert into inf_update
( UPDID,
UPDTYPE,
RECCREATEON,
RECCREATEBY,
CONTENT,
LINK,
DISPLAY,
LANGUAGE ) values
( v_updid,
v_updtype,
now(),
new.reccreateby,
new.title ,
new.link,
''Y'',
v_language );
if not found then
raise exception ''Unable to insert record to
inf_update.'';
end if;
end;
End;
' LANGUAGE 'plpgsql';
CREATE TRIGGER bf_ins_inf_article
BEFORE INSERT ON inf_article FOR EACH ROW
EXECUTE PROCEDURE pg_fct_bf_ins_inf_article();
So the Line 9 is this line:
select (''UPD'' || int2hex(nextval(''updid''), 8)) as
v_updid;
The table column name is updid, so I define the variable as v_updid,
which should not conflict with the table column name.
Li Hao
Senior Consultant
Sageway Computer Solution Pte Ltd
38A Jalan Pemimpin #02-05
Wisdom Industrial Building
Singapore 577179
Mobile: 65-96631471
Tel: 65-62583561
-----Original Message-----
From: Tomasz Myrta [mailto:jasiek@klaster.net]
Sent: Monday, April 28, 2003 4:54 PM
To: lihao
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Pl Pgsql problem
Dnia 2003-04-28 11:15, Uz.ytkownik lihao napisa?:
> ERROR: parser: parse error at or near "$1"
This error is often caused by variables defined with the same names as
table columns.
Anyway it would be easier for us if you write source of line which
caused error.
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pl Pgsql problem
2003-04-28 09:15 Pl Pgsql problem lihao <lihao@sageway.com.sg>
2003-04-28 08:54 ` Re: Pl Pgsql problem Tomasz Myrta <jasiek@klaster.net>
2003-04-28 09:29 ` Re: Pl Pgsql problem lihao <lihao@sageway.com.sg>
@ 2003-04-28 09:20 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-28 10:14 ` Re: Pl Pgsql problem lihao <lihao@sageway.com.sg>
0 siblings, 1 reply; 5+ messages in thread
From: Tomasz Myrta @ 2003-04-28 09:20 UTC (permalink / raw)
To: lihao <lihao@sageway.com.sg>; +Cc: pgsql-sql
Dnia 2003-04-28 11:29, Uz.ytkownik lihao napisa?:
> So the Line 9 is this line:
> select (''UPD'' || int2hex(nextval(''updid''), 8)) as
> v_updid;
Did you mean:
select into v_updid ''UPD'' || int2hex(nextval(''updid''), 8);
?
Tomasz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Pl Pgsql problem
2003-04-28 09:15 Pl Pgsql problem lihao <lihao@sageway.com.sg>
2003-04-28 08:54 ` Re: Pl Pgsql problem Tomasz Myrta <jasiek@klaster.net>
2003-04-28 09:29 ` Re: Pl Pgsql problem lihao <lihao@sageway.com.sg>
2003-04-28 09:20 ` Re: Pl Pgsql problem Tomasz Myrta <jasiek@klaster.net>
@ 2003-04-28 10:14 ` lihao <lihao@sageway.com.sg>
0 siblings, 0 replies; 5+ messages in thread
From: lihao @ 2003-04-28 10:14 UTC (permalink / raw)
To: 'Tomasz Myrta' <jasiek@klaster.net>; +Cc: pgsql-sql
Hi,
What I try to get is
venus=# select ('UPD' || int2hex(nextval('updid'), 8)) as v_updid;
v_updid
-------------
UPD0000145C
(1 row)
updid is a sequence. I am try to generate a index with character and
sequence number.
Anyway, I solve it by change the script to :
select (''UPD'' || int2hex(nextval(''updid''), 8)) into v_updid;
Thank you.
Li Hao
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Tomasz Myrta
Sent: Monday, April 28, 2003 5:20 PM
To: lihao
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Pl Pgsql problem
Dnia 2003-04-28 11:29, Uz.ytkownik lihao napisa?:
> So the Line 9 is this line:
> select (''UPD'' || int2hex(nextval(''updid''), 8)) as
> v_updid;
Did you mean:
select into v_updid ''UPD'' || int2hex(nextval(''updid''), 8);
?
Tomasz
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-04-28 10:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-28 09:15 Pl Pgsql problem lihao <lihao@sageway.com.sg>
2003-04-28 08:54 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-28 09:29 ` lihao <lihao@sageway.com.sg>
2003-04-28 09:20 ` Tomasz Myrta <jasiek@klaster.net>
2003-04-28 10:14 ` lihao <lihao@sageway.com.sg>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox