Thread: Pl Pgsql problem
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">I am using 7.1.3 on RedHat Linux 7.2.</span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">I try to create a trigger on the table, I wrote a function in <span
class="SpellE">plpgsql</span>,which always fail on this <span class="SpellE">sql</span> command:</span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><span class="GramE"><font
face="Arial"size="1"><span lang="EN-US"
style="font-size:9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">select</span></font></span><fontface="Arial"
size="1"><spanlang="EN-US" style="font-size:9.0pt;mso-bidi-font-size:
10.0pt;font-family:Arial"> (''UPD'' || int2hex(<span class="SpellE">nextval</span>(''<span
class="SpellE">updid</span>''),8)) as <span class="SpellE">v_updid</span>;</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">The function is like this:</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><span class="GramE"><font
face="Arial"size="1"><span lang="EN-US"
style="font-size:9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">begin</span></font></span><fontface="Arial"
size="1"><spanlang="EN-US" style="font-size:9.0pt;mso-bidi-font-size:
10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes"> </span><span
class="GramE">select</span>(''UPD'' || int2hex(<span class="SpellE">nextval</span>(''<span
class="SpellE">updid</span>''),8)) as <span class="SpellE">v_updid</span>;</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes"> </span><span
class="GramE">if</span>not found then</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes"> </span><span
class="GramE">raise</span>exception ''Unable to get <span class="SpellE">updid</span>.'';</span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes"> </span><span
class="GramE">end</span>if;</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes"> </span><span
class="GramE">end</span>;</span></font><pclass="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">It <span class="GramE">give</span> me this error when I trigger the
function:</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">NOTICE:<span style="mso-spacerun:yes"> </span>Error occurred while
executingPL/<span class="SpellE">pgSQL</span> function <span
class="SpellE">pg_fct_bf_ins_inf_article</span></span></font><pclass="MsoNormal"><font face="Arial" size="1"><span
lang="EN-US"style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">NOTICE:<span style="mso-spacerun:yes"> </span>line 9 at SQL
statement<spanstyle="mso-tab-count:1"> </span>-- the SQL select command</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">ERROR:<span style="mso-spacerun:yes"> </span>parser: parse error at
ornear "$1"</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">I can execute the SQL without problem. </span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><<</span></font><p class="MsoNormal"><span class="GramE"><font
face="Arial"size="1"><span lang="EN-US"
style="font-size:9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">select</span></font></span><fontface="Arial"
size="1"><spanlang="EN-US" style="font-size:9.0pt;mso-bidi-font-size:
10.0pt;font-family:Arial"> ('UPD' || int2hex(<span class="SpellE">nextval</span>('<span class="SpellE">updid</span>'),
8))as <span class="SpellE">v_updid</span>;</span></font><p class="MsoNormal"><font face="Arial" size="1"><span
lang="EN-US"style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">>></span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">Can anyone help me to see what <span class="GramE">is the problem
here</span>.</span></font><pclass="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoAutoSig"><font face="Times New Roman"
size="2"><spanlang="EN-US" style="font-size:10.5pt;mso-no-proof:yes">Li Hao</span></font><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-US" style="font-size:12.0pt"> </span></font></div>
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