From: Erik Jones <erik@myemma.com>
To: Rodrigo De Leon <rdeleonp@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: CREATE TABLE AS inside of a function
Date: Fri, 21 Jul 2006 16:04:37 -0500
Message-ID: <44C14165.2040304@myemma.com> (raw)
In-Reply-To: <a55915760607211332ye89afd4m9bb2ad456fcef99b@mail.gmail.com>
References: <6EA72AC41F81D242A38AAF505523A94D022DF6@kappa.acrodex.net>
<a55915760607211332ye89afd4m9bb2ad456fcef99b@mail.gmail.com>
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.htmlhttp://www.postgresql.org/docs/8.1/interactive/triggers.htmlhttp://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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: erik@myemma.com, rdeleonp@gmail.com
Subject: Re: CREATE TABLE AS inside of a function
In-Reply-To: <44C14165.2040304@myemma.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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