pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCREATE TABLE AS inside of a function
5+ messages / 4 participants
[nested] [flat]
* CREATE TABLE AS inside of a function
@ 2006-07-21 19:14 Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-21 20:32 ` Re: CREATE TABLE AS inside of a function Rodrigo De Leon <rdeleonp@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Nikiforuk @ 2006-07-21 19:14 UTC (permalink / raw)
To: pgsql-sql
So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below.
CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$
DECLARE
lv RECORD;
BEGIN
FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
CREATE TABLE rgio_$lv AS
SELECT ldev
FROM ldevrg
WHERE rg='$lv';
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
Thanks,
Kevin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CREATE TABLE AS inside of a function
2006-07-21 19:14 CREATE TABLE AS inside of a function Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-21 20:32 ` Rodrigo De Leon <rdeleonp@gmail.com>
2006-07-21 21:04 ` Re: CREATE TABLE AS inside of a function Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 5+ messages in thread
From: Rodrigo De Leon @ 2006-07-21 20:32 UTC (permalink / raw)
To: pgsql-sql
On 7/21/06, Kevin Nikiforuk <Kevin.Nikiforuk@acrodex.com> wrote:
> So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below.
>
> CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$
> DECLARE
> lv RECORD;
>
> BEGIN
> FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
> CREATE TABLE rgio_$lv AS
> SELECT ldev
> FROM ldevrg
> WHERE rg='$lv';
> END LOOP;
> RETURN 1;
> END;
> $$ LANGUAGE plpgsql;
>
> Thanks,
> Kevin
See:
http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
Regards,
Rodrigo
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CREATE TABLE AS inside of a function
2006-07-21 19:14 CREATE TABLE AS inside of a function Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-21 20:32 ` Re: CREATE TABLE AS inside of a function Rodrigo De Leon <rdeleonp@gmail.com>
@ 2006-07-21 21:04 ` Erik Jones <erik@myemma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Erik Jones @ 2006-07-21 21:04 UTC (permalink / raw)
To: Rodrigo De Leon <rdeleonp@gmail.com>; +Cc: pgsql-sql
Rodrigo De Leon wrote:
> On 7/21/06, Kevin Nikiforuk <Kevin.Nikiforuk@acrodex.com> wrote:
>> So now that I've got my loops working, on to my next newbie
>> question. I've created my function and in it, I want to loop through
>> the results of a select and for each value of my loop counter, I want
>> to create a new table, but I can't figure out how to use a variable
>> in the name of the new table, see below.
>>
>> CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$
>> DECLARE
>> lv RECORD;
>>
>> BEGIN
>> FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
>> CREATE TABLE rgio_$lv AS
>> SELECT ldev
>> FROM ldevrg
>> WHERE rg='$lv';
>> END LOOP;
>> RETURN 1;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> Thanks,
>> Kevin
>
> See:
> http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
Also, I really recommend enough that you read chapters 32. Extending
SQL, 33. Triggers, and 36. Pl/pgSQL in their entirety. In fact, to keep
up with the linking to them for you:
http://www.postgresql.org/docs/8.1/interactive/extend.html
http://www.postgresql.org/docs/8.1/interactive/triggers.html
http://www.postgresql.org/docs/8.1/interactive/plpgsql.html
And, so that I don't feel like I'm becoming one of those rtfm jerks I
always complain about: what you need to do is place your CREATE TABLE
statement in an EXECUTE directive like so (inside the the FOR body):
EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS
SELECT ldev
FROM ldevrg
WHERE rg=\'' || $lv || '\';' -- this line could have also been:
WHERE rg=' || quote_literal($lv) || ';'
EXECUTE takes a query in a string to execute and you need to use string
concatenation to build the string if you're using variables from the
function in the query. Pl/pgSQL doesn't have any variable substitution
inside of strings (like in double quoted string in PHP) which is why you
need to use the concatenation bit.
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CREATE TABLE AS inside of a function
@ 2006-07-24 20:49 Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-24 22:04 ` Re: CREATE TABLE AS inside of a function Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 1 reply; 5+ messages in thread
From: Kevin Nikiforuk @ 2006-07-24 20:49 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; Rodrigo De Leon <rdeleonp@gmail.com>; +Cc: pgsql-sql
So, I've changed my code as Erik suggested:
CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$
DECLARE
lv RECORD;
BEGIN
FOR lv IN SELECT DISTINCT rg
FROM ldevrg
LOOP
EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS
SELECT ldev
FROM ldevrg
WHERE rg=' || quote_literal($lv) || ';'
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;
And I get:
psql:rgio.sql:32: ERROR: syntax error at or near "$" at character 33
QUERY: SELECT 'CREATE TABLE rgio_' || $ $1 || ' AS
SELECT ldev
FROM ldevrg
WHERE rg=' || quote_literal($ $2 ) || ';' END LOOP
CONTEXT: SQL statement in PL/PgSQL function "rgio" near line 23
psql:rgio.sql:32: LINE 1: SELECT 'CREATE TABLE rgio_' || $ $1 || ' AS
psql:rgio.sql:32: ^
________________________________
From: pgsql-sql-owner@postgresql.org on behalf of Erik Jones
Sent: Fri 7/21/2006 3:04 PM
To: Rodrigo De Leon
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] CREATE TABLE AS inside of a function
Rodrigo De Leon wrote:
> On 7/21/06, Kevin Nikiforuk <Kevin.Nikiforuk@acrodex.com> wrote:
>> So now that I've got my loops working, on to my next newbie
>> question. I've created my function and in it, I want to loop through
>> the results of a select and for each value of my loop counter, I want
>> to create a new table, but I can't figure out how to use a variable
>> in the name of the new table, see below.
>>
>> CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$
>> DECLARE
>> lv RECORD;
>>
>> BEGIN
>> FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP
>> CREATE TABLE rgio_$lv AS
>> SELECT ldev
>> FROM ldevrg
>> WHERE rg='$lv';
>> END LOOP;
>> RETURN 1;
>> END;
>> $$ LANGUAGE plpgsql;
>>
>> Thanks,
>> Kevin
>
> See:
> http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
Also, I really recommend enough that you read chapters 32. Extending
SQL, 33. Triggers, and 36. Pl/pgSQL in their entirety. In fact, to keep
up with the linking to them for you:
http://www.postgresql.org/docs/8.1/interactive/extend.html
http://www.postgresql.org/docs/8.1/interactive/triggers.html
http://www.postgresql.org/docs/8.1/interactive/plpgsql.html
And, so that I don't feel like I'm becoming one of those rtfm jerks I
always complain about: what you need to do is place your CREATE TABLE
statement in an EXECUTE directive like so (inside the the FOR body):
EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS
SELECT ldev
FROM ldevrg
WHERE rg=\'' || $lv || '\';' -- this line could have also been:
WHERE rg=' || quote_literal($lv) || ';'
EXECUTE takes a query in a string to execute and you need to use string
concatenation to build the string if you're using variables from the
function in the query. Pl/pgSQL doesn't have any variable substitution
inside of strings (like in double quoted string in PHP) which is why you
need to use the concatenation bit.
--
erik jones <erik@myemma.com>
software development
emma(r)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: CREATE TABLE AS inside of a function
2006-07-24 20:49 Re: CREATE TABLE AS inside of a function Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
@ 2006-07-24 22:04 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2006-07-24 22:04 UTC (permalink / raw)
To: Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>; +Cc: Erik Jones <erik@myemma.com>; Rodrigo De Leon <rdeleonp@gmail.com>; pgsql-sql
On Mon, 24 Jul 2006, Kevin Nikiforuk wrote:
> So, I've changed my code as Erik suggested:
>
> CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$
> DECLARE
> lv RECORD;
>
> BEGIN
> FOR lv IN SELECT DISTINCT rg
> FROM ldevrg
> LOOP
>
> EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS
> SELECT ldev
> FROM ldevrg
> WHERE rg=' || quote_literal($lv) || ';'
I think you want something like lv.rg (no special punctuation) rather than
$lv in the above.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-07-24 22:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-21 19:14 CREATE TABLE AS inside of a function Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-21 20:32 ` Rodrigo De Leon <rdeleonp@gmail.com>
2006-07-21 21:04 ` Erik Jones <erik@myemma.com>
2006-07-24 20:49 Re: CREATE TABLE AS inside of a function Kevin Nikiforuk <Kevin.Nikiforuk@Acrodex.com>
2006-07-24 22:04 ` Stephan Szabo <sszabo@megazone.bigpanda.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