From: lihao <lihao@sageway.com.sg>
To: 'Tomasz Myrta' <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Pl Pgsql problem
Date: Mon, 28 Apr 2003 17:29:14 +0800
Message-ID: <001801c30d68$a48d7640$f301a8c0@pc66> (raw)
In-Reply-To: <3EACEC28.1090208@klaster.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lihao@sageway.com.sg, jasiek@klaster.net
Subject: Re: Pl Pgsql problem
In-Reply-To: <001801c30d68$a48d7640$f301a8c0@pc66>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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