Postgres Pro
Downloads
Home   >   mailing lists

Thread: EXECUTE of a 'create table' string is not happening

EXECUTE of a 'create table' string is not happening

From
Ralph Smith
Date:
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
_________________________

Re: EXECUTE of a 'create table' string is not happening

From
"David Johnston"
Date:

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
_________________________

Re: EXECUTE of a 'create table' string is not happening

From
Pavel Stehule
Date:
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
> _________________________
>

CLOSURE: EXECUTE of a 'create table' string is not happening

From
Ralph Smith
Date:
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
_________________________