Thread: pgpsql help
Hi,
I have a pgpsql function:
create or replace function new_uri(varchar,varchar,varchar) returns
integer as '
declare
src alias for $1;
tit alias for $2;
uri alias for $3;
article_id integer := nextval(''articleid'');
src_id integer;
state_id integer;
urit_id integer;
begin
select into src_id id from sources where name = src;
select into state_id id from state where name = ''collected'';
select into urit_id id from uritype where name = ''original html'';
raise notice ''insert into article (id,source,title,state) values
(%,%,%,%)'',article_id,src_id,tit,state_id;
insert into article (id,source,title,state) values
(article_id,src_id,tit,state_id);
raise notice ''insert into uri (articleid,uri,uritype) values
(%,%,%)'',article_id,uri,urit_id;
insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id);
return 1;
end;
' language 'plpgsql';
An i also have some output:
ifa=> \i new_uri.sql
CREATE FUNCTION
ifa=> select new_uri('index.hu','juscsenko','index.hu/foobar');
NOTICE: insert into article (id,source,title,state) values
(56,3,juscsenko,1)
NOTICE: insert into uri (articleid,uri,uritype) values
(56,index.hu/foobar,3)
ERROR: syntax error at or near "$1" at character 14
CONTEXT: PL/pgSQL function "new_uri" line 17 at SQL statement
ifa=>
What should i do?
thx,
War_New
Maybe it is because 'name' is a sql 92/99 non-reserved / key word?
On Mon, Jan 17, 2005 at 05:46:20AM +0100, LENGYEL Zoltan wrote:
> Hi,
>
> I have a pgpsql function:
>
> create or replace function new_uri(varchar,varchar,varchar) returns
> integer as '
> declare
> src alias for $1;
> tit alias for $2;
> uri alias for $3;
> article_id integer := nextval(''articleid'');
> src_id integer;
> state_id integer;
> urit_id integer;
> begin
>
> select into src_id id from sources where name = src;
> select into state_id id from state where name = ''collected'';
> select into urit_id id from uritype where name = ''original html'';
> raise notice ''insert into article (id,source,title,state) values
> (%,%,%,%)'',article_id,src_id,tit,state_id;
> insert into article (id,source,title,state) values
> (article_id,src_id,tit,state_id);
> raise notice ''insert into uri (articleid,uri,uritype) values
> (%,%,%)'',article_id,uri,urit_id;
> insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id);
>
> return 1;
> end;
> ' language 'plpgsql';
>
> An i also have some output:
>
> ifa=> \i new_uri.sql
> CREATE FUNCTION
> ifa=> select new_uri('index.hu','juscsenko','index.hu/foobar');
> NOTICE: insert into article (id,source,title,state) values
> (56,3,juscsenko,1)
> NOTICE: insert into uri (articleid,uri,uritype) values
> (56,index.hu/foobar,3)
> ERROR: syntax error at or near "$1" at character 14
> CONTEXT: PL/pgSQL function "new_uri" line 17 at SQL statement
> ifa=>
>
> What should i do?
>
>
> thx,
>
> War_New
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
On Mon, Jan 17, 2005 at 05:46:20AM +0100, LENGYEL Zoltan wrote: > uri alias for $3; ... > insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id); PostgreSQL 8.0 will give more context for the error, making it easier to spot the problem: ERROR: syntax error at or near "$1" at character 14 QUERY: insert into $1 (articleid, $2 ,uritype) values ( $3 , $4 , $5 ) CONTEXT: PL/pgSQL function "new_uri" line 20 at SQL statement LINE 1: insert into $1 (articleid, $2 ,uritype) values ( $3 , $4 ,... The variable "uri" is apparently also the name of a table and a column in that table. Use a different name for the variable. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
"Mike G." <mike@thegodshalls.com> writes:
> Maybe it is because 'name' is a sql 92/99 non-reserved / key word?
No, it's because of careless choice of plpgsql variable names.
>> declare
>> uri alias for $3;
>> ...
>> insert into uri (articleid,uri,uritype) values (article_id,uri,urit_id);
^^^ ^^^ ^^^
Not being exceedingly bright, plpgsql will attempt to substitute its
variable into all three matches in this command. Two of those are
of course wrong and lead to syntax errors.
First rule of plpgsql programming: do not use plpgsql variable names
that duplicate any of the SQL-level names (tables, columns, functions,
etc) you intend to use in the function.
regards, tom lane
If anyone is interested in beta testing a new win32 based admin tool for Postgresql 8.x please sign up here: http://www.amsoftwaredesign.com/betatest.html Screen shots are available here: http://www.amsoftwaredesign.com/pg_ss.asp.asp We will be accepting 30 beta testers and each tester will receive a free one year subscription (starting at 1.0 release). Upon release the software will sell for $29.99 for a one year subscription. After one year, subscribers will be able to renew for $15.00 This software is geared to DBAs and developers and includes such features as: Function Version Control Advanced Function Editor with code completion and param hinting and the ability to print ability to print result sets. Export result sets to PDF,Excel,XML, Comma separated,Tab separated or as insert statements Tabbed Enterprise Manager, each database opens in it's own tab (makes working on several copies a snap and eliminates the confusion of everything in a single object tree). MDI style application which is familiar to windows users. MDI window task bar that filters based on the Enterprise Manager tab selection. Will run on Windows 98 and up. Note: this software is geared for Corporate/Home Windows users and is not cross platform, though it does run on the latest version of WINE (we will not support running on WINE) Thanks, Tony Caduto AM Software Design http://www.amsoftwaredesign.com