pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGeneric Function
4+ messages / 3 participants
[nested] [flat]
* Generic Function
@ 2005-03-14 18:15 lucas@presserv.org
0 siblings, 1 reply; 4+ messages in thread
From: lucas@presserv.org @ 2005-03-14 18:15 UTC (permalink / raw)
To: pgsql-sql
Hi,
Can I built a generic function like:
CREATE FUNCTION f (text) RETURNS TEXT as
$$
return 'select * from $1';
$$
I know its impossible as writed. Also I have looked for EXECUTE procedure but it
not run the correct function.
Is there a way to construct this clause? Using plpgsql/pltcl/anything.... ???
Thanks
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Generic Function
@ 2005-03-15 11:48 George Weaver <gweaver@shaw.ca>
parent: lucas@presserv.org
0 siblings, 1 reply; 4+ messages in thread
From: George Weaver @ 2005-03-15 11:48 UTC (permalink / raw)
To: lucas@presserv.org; pgsql-sql
----- 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';
> $$
> I know its impossible as writed. Also I have looked for EXECUTE procedure
> but it
> not run the correct function.
If you show us what you've tried and the results you received we may be able
to help more.
Some points:
1. To create a string to run with EXECUTE you would need to concatenate
the above phrase:
(Assuming "query" has been declared as text): query := 'Select *
from ' || $1;
2. Since you're likely returning multiple rows, you need to write your
function as a set-returning
function. See:
http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING
and Executing Dynamic Commands in
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-PERFORM
> Is there a way to construct this clause? Using plpgsql/pltcl/anything....
> ???
>
> Thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Generic Function
@ 2005-03-16 14:00 lucas@presserv.org
parent: George Weaver <gweaver@shaw.ca>
0 siblings, 1 reply; 4+ messages in thread
From: lucas@presserv.org @ 2005-03-16 14:00 UTC (permalink / raw)
To: George Weaver <gweaver@shaw.ca>; +Cc: 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.
...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Generic Function
@ 2005-03-16 14:28 PFC <lists@boutiquenumerique.com>
parent: lucas@presserv.org
0 siblings, 0 replies; 4+ messages in thread
From: PFC @ 2005-03-16 14:28 UTC (permalink / raw)
To: lucas@presserv.org, "George Weaver" <gweaver@shaw.ca>; +Cc: pgsql-sql
Look in the plpgsql docs on EXECUTE.
But for something that simple, why don't you just generate a query ?
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-03-16 14:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-14 18:15 Generic Function lucas@presserv.org
2005-03-15 11:48 ` George Weaver <gweaver@shaw.ca>
2005-03-16 14:00 ` lucas@presserv.org
2005-03-16 14:28 ` PFC <lists@boutiquenumerique.com>
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