Обсуждение: 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

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_articleurlvarchar(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
''Unableto 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
toget 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

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

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