Re: [SQL] CREATE TABLE AS inside of a function

2006-07-24 Thread Kevin Nikiforuk 
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: [EMAIL PROTECTED] 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 [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



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] CREATE TABLE AS inside of a function

2006-07-24 Thread Stephan Szabo 
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.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

[SQL] CREATE TABLE AS inside of a function

2006-07-21 Thread Kevin Nikiforuk 
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

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] CREATE TABLE AS inside of a function

2006-07-21 Thread Rodrigo De Leon 

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

Regards,

Rodrigo

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] CREATE TABLE AS inside of a function

2006-07-21 Thread Erik Jones 

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