Re: Pl Pgsql problem

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(at)klaster(dot)net]
Sent: Monday, April 28, 2003 4:54 PM
To: lihao
Cc: pgsql-sql(at)postgresql(dot)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