From: Ben K. <bkim@coe.tamu.edu>
To: Gregory S. Williamson <gsw@globexplorer.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Linked List
Date: Sun, 30 Apr 2006 23:49:45 -0500 (CDT)
Message-ID: <Pine.GSO.4.64.0604302327350.27523@coe.tamu.edu> (raw)
In-Reply-To: <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>
References: <IEELLOAPJFACBDBGELGHKEJPEDAA.ray@madigans.org>
<Pine.GSO.4.64.0604302014590.22531@coe.tamu.edu>
<71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>
> The pgsql function is compiled and wouldn't know how to handle a table
>name as a variable.
> If you rewrite the SQL to use the 'EXECUTE' statement I think you could
>do this, something along the lines of (untested):
> EXECUTE ''INSERT INTO '' || quote_ident(tmplist) || '' (SELECT * FROM
>links WHERE p=x)'';
Thanks. Yet, if I give the table name as the argument, I get the same
error.
drop function traverse(integer, text);
create or replace function traverse (integer, text)
returns integer as
$$
declare
x int;
tname alias for $2;
begin
x := $1;
while x is not null loop
select n into x from links where p = x;
insert into tmplink (select * from links where p=x);
EXECUTE ''INSERT INTO '' || quote_ident(tname) || '' (SELECT * FROM
links WHERE p=x)'';
end loop;
return 1 ;
end;
$$
language plpgsql;
The above gives the following error. Please note that the first and
second args are mixed up now. I tested it only on 8.0 (FC 4) and didn't on
8.1 and wonder whether it's a bug or I may be doing something wrong.
Using tname or $2 doesn't change the result.
========================================================================
# select traverse(0, 'links2');
ERROR: syntax error at or near "INSERT" at character 11
QUERY: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT * FROM
links WHERE p= $2 )''
CONTEXT: PL/pgSQL function "traverse" line 10 at execute statement
LINE 1: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT ...
========================================================================
Regards,
Ben K.
Developer
http://benix.tamu.edu
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: bkim@coe.tamu.edu, gsw@globexplorer.com
Subject: Re: Linked List
In-Reply-To: <Pine.GSO.4.64.0604302327350.27523@coe.tamu.edu>
* 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