Re: 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
couldhave 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)