EXECUTE of a 'create table' string is not happening

A little lost but the first thing that stands out is that you are attempting
to create an actual table instead of a temporary table. Not sure if that
difference is meaningful to the function but procedurally is there a reason
to create the permanent table instead of a temporary one?

If you do need a permanent table would you be able to generate the data as
part of routine maintenance and/or via triggers instead of building out the
entire (or portion) of the table each time?

I do not think you have provided enough code to get good feedback. The
entire function would probably help - though maybe a simplified version but
one that still exhibits the behavior in question.

You also do not provide the minor release level which may be relevant.

David J.

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ralph Smith
Sent: Tuesday, February 22, 2011 2:05 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] EXECUTE of a 'create table' string is not happening

Hi,

I'm passing a tablename and two columnnames into a function so that I can
SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then alter
the names of two columns.

When I 'build' the function and then run my query to use the function w/ a
different offset it works the first time.
The first time ONLY. But actually it doesn't work, it just doesn't error
the first run.
Subsequent runs tell me that relation ######## doesn't exist, at the
FOR...LOOP line, after ALTERing the table.

I build the string and it's good:
CREATE TABLE temp_gentokenstable AS SELECT businessid, publicname FROM
businesscontact ORDER BY businessid OFFSET 200000 LIMIT 10000 ;

I'm forced to use v7.4.

If this is a known error, can I (with the version ; ^) ) get around it?

Ultimately I need to FOR..LOOP through through records and the table and
cols will change. Any suggestions???

THANKS!

--

Ralph
_________________________

2011/2/22 Ralph Smith <rsmith@10kinfo.com>:

Hi,

I'm passing a tablename and two columnnames into a function so that I can
SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then alter
the names of two columns.

When I 'build' the function and then run my query to use the function w/ a
different offset it works the first time.
The first time ONLY.  But actually it doesn't work, it just doesn't error
the first run.
Subsequent runs tell me that relation ######## doesn't exist, at the
FOR...LOOP line, after ALTERing the table.

I build the string and it's good:
CREATE TABLE temp_gentokenstable AS SELECT businessid, publicname FROM
businesscontact ORDER BY businessid OFFSET 200000 LIMIT 10000 ;

I'm forced to use v7.4.

If this is a known error, can I (with the version  ; ^)  ) get around it?

Ultimately I need to FOR..LOOP through through records and the table and
cols will change.  Any suggestions???

use a EXECUTE statement and FOR IN EXECUTE statement

regards

Pavel Stehule

This worked!!!
FOR TableRec IN EXECUTE ExecuteString LOOP

THANKS ALL!!!
Ralph

p.s. The reason we're still using 7.4 is that some system logs were
trashed and we NEED that data. All but the BLOGS have been recovered,
and there lies the problem.
=====================================

Pavel Stehule wrote:

2011/2/22 Ralph Smith <rsmith@10kinfo.com>:

Hi,

I'm passing a tablename and two columnnames into a function so that I can
SELECT Records in a FOR LOOP using 'fixed' field names.
Using the passed params I construct the create table command and then alter
the names of two columns.

When I 'build' the function and then run my query to use the function w/ a
different offset it works the first time.
The first time ONLY. But actually it doesn't work, it just doesn't error
the first run.
Subsequent runs tell me that relation ######## doesn't exist, at the
FOR...LOOP line, after ALTERing the table.

I build the string and it's good:
CREATE TABLE temp_gentokenstable AS SELECT businessid, publicname FROM
businesscontact ORDER BY businessid OFFSET 200000 LIMIT 10000 ;

I'm forced to use v7.4.

If this is a known error, can I (with the version ; ^) ) get around it?

Ultimately I need to FOR..LOOP through through records and the table and
cols will change. Any suggestions???

use a EXECUTE statement and FOR IN EXECUTE statement

regards

Pavel Stehule

THANKS!

--

Ralph
_________________________

--

Ralph
_________________________