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 <[EMAIL PROTECTED]>:
----- Original Message ----- From: <[EMAIL PROTECTED]> 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.
...
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
