Thread: Pl Pgsql problem

Pl Pgsql problem

From
"lihao"
Date:
<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> 

Re: Pl Pgsql problem

From
Tomasz Myrta
Date:
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



Re: Pl Pgsql problem

From
"lihao"
Date:
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



Re: Pl Pgsql problem

From
Tomasz Myrta
Date:
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



Re: Pl Pgsql problem

From
"lihao"
Date:
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