Rodrigo De Leon wrote:
On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
software development
emma(r)


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to