pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreation of a table with dynamic name from inside a pgpsql function
2+ messages / 2 participants
[nested] [flat]
* Creation of a table with dynamic name from inside a pgpsql function
@ 2002-05-28 15:53 Michael Beckstette <mbeckste@TechFak.Uni-Bielefeld.DE>
0 siblings, 1 reply; 2+ messages in thread
From: Michael Beckstette @ 2002-05-28 15:53 UTC (permalink / raw)
To: pgsql-sql
Hi,
i have tried to create a table with a name specified by a calling parameter of
a pgsql function. Something like this:
CREATE FUNCTION test (varchar) RETURNS TEXT AS '
DECLARE
table_name ALIAS FOR $1;
query_string varchar;
BEGIN
query_string:= ''CREATE TABLE temp1 AS SELECT * FROM ''||
quote_ident(table_name) || '' WHERE (hsp_rank=1 AND hsp_evalue<=0.001)'';
RAISE NOTICE ''Query: %'',query_string;
EXECUTE query_string;
RETURN table_name;
END;
' LANGUAGE 'plpgsql';
with the following result:
prod2_db=# select test('blasthits_obj_174_q122_db123');
NOTICE: Query: CREATE TABLE temp1 AS SELECT * FROM
blasthits_obj_174_q122_db123 WHERE (hsp_rank=1 AND hsp_evalue<=0.001)
ERROR: EXECUTE of SELECT ... INTO is not implemented yet
prod2_db=#
i read in the docs, that an SELECT INTO is not fully supported, but how can i
solve this problem ? Any idea or workaround ?
Regards
Michael Beckstette
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Creation of a table with dynamic name from inside a pgpsql function
@ 2002-05-28 17:33 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Beckstette <mbeckste@TechFak.Uni-Bielefeld.DE>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-05-28 17:33 UTC (permalink / raw)
To: Michael Beckstette <mbeckste@TechFak.Uni-Bielefeld.DE>; +Cc: pgsql-sql
"Michael Beckstette" <mbeckste@TechFak.Uni-Bielefeld.DE> writes:
> query_string:= ''CREATE TABLE temp1 AS SELECT * FROM ''||
> quote_ident(table_name) || '' WHERE (hsp_rank=1 AND hsp_evalue<=0.001)'';
> RAISE NOTICE ''Query: %'',query_string;
> EXECUTE query_string;
> prod2_db=# select test('blasthits_obj_174_q122_db123');
> NOTICE: Query: CREATE TABLE temp1 AS SELECT * FROM
> blasthits_obj_174_q122_db123 WHERE (hsp_rank=1 AND hsp_evalue<=0.001)
> ERROR: EXECUTE of SELECT ... INTO is not implemented yet
Update to 7.2.1. The above should be allowed, but was mistakenly
rejected by 7.2.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-05-28 17:33 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-05-28 15:53 Creation of a table with dynamic name from inside a pgpsql function Michael Beckstette <mbeckste@TechFak.Uni-Bielefeld.DE>
2002-05-28 17:33 ` Tom Lane <tgl@sss.pgh.pa.us>
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