Date: Sun, 30 Apr 2006 23:49:45 -0500 (CDT)
From: "Ben K." <bkim@coe.tamu.edu>
To: "Gregory S. Williamson" <gsw@globexplorer.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Linked List
In-Reply-To: 
 <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>
Message-ID: <Pine.GSO.4.64.0604302327350.27523@coe.tamu.edu>
References: <IEELLOAPJFACBDBGELGHKEJPEDAA.ray@madigans.org>
	<Pine.GSO.4.64.0604302014590.22531@coe.tamu.edu>
	<71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed
Received-SPF: pass (smtp-relay.tamu.edu: 165.91.232.6 is authenticated by a
	trusted mechanism)

> 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