Re: Generic Function - Mailing list pgsql-sql

From lucas@presserv.org
Subject Re: Generic Function
Date
Msg-id 20050316110057.hj7k01plv1ck4g08@www.presserv.org
Whole thread Raw
In response to Re: Generic Function  (George Weaver <gweaver@shaw.ca>)
Responses Re: Generic Function
List pgsql-sql
Oh sorry.
I was not clearly.
I've wanted to create a function that suport to select a "parameter variable"
table. Like: return 'select * from $1'. The Postgresql does not suport this sql function becouse the $1 variable is
considerate as a table... the Postgresql return an error like: The table "$1"
doesn't exist.
Then there is no way to construct this function as SQL function, but I could
make it in PL/TCL function and execute normaly look:

set search_path to public;
create table tb1(vl text);
insert into tb1 values ('aaa');
create table tb2(vl text);
insert into tb2 values ('bbb');

CREATE or REPLACE FUNCTION select_table(text, text,  text) returns text as
$$ set schema_name $1; set table_name $2; set field_name $3; set select_query "select $field_name AS select_table from
";set point "."; spi_exec $select_query$schema_name$point$table_name; return $select_table;
 
$$ language 'pltcl';
Then:
SELECT select_table('public','tb1','vl');
SELECT select_table('public','tb2','vl');

The spi_exec execute the query as a variable ($select_query$...) and 
the return
of this query (select $field_name AS select_table) will be the variable
"select_table" for the pl/tcl function. Then I return this variable (return
$select_table).

Is it right?! Is there a better way to make it?
The Pl/Pgsql can built this function? And the SQL Standard?

Thanks...

Quoting George Weaver <gweaver@shaw.ca>:
>
> ----- Original Message -----
> From: <lucas@presserv.org>
> To: <pgsql-sql@postgresql.org>
> Sent: Monday, March 14, 2005 12:15 PM
> Subject: [SQL] Generic Function
>
>
>> Hi,
>> Can I built a generic function like:
>> CREATE FUNCTION f (text) RETURNS TEXT as
>>  $$
>>   return 'select * from $1';
>>  $$
...

>
> If you show us what you've tried and the results you received we may be able
> to help more.
...





pgsql-sql by date:

Previous
From: Ragnar Hafstað
Date:
Subject: Re: Scheme not dropping
Next
From: PFC
Date:
Subject: Re: Generic Function