Variable column name

In response to "Bob Pawley" <rjpawley@shaw.ca>:

I want to add information to multiple columns (20 – 40) by employing a loop. Each pass of the loop will populate one column with an array.

I have tried and I have read that variables can not be used to control column names.

Is there a means of working around this restriction other than creating 20 to 40 hard coded statements??

You can generate dynamic SQL statements with plpgsql.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-----Original Message-----
From: Bill Moran
Sent: Thursday, September 01, 2011 7:59 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

In response to "Bob Pawley" <rjpawley@shaw.ca>:

I want to add information to multiple columns (20 – 40) by employing a
loop. Each pass of the loop will populate one column with an array.

I have tried and I have read that variables can not be used to control
column names.

Is there a means of working around this restriction other than creating 20
to 40 hard coded statements??

You can generate dynamic SQL statements with plpgsql.

Would it be possible for you to point me to an example??

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Sep 1, 2011, at 9:04 AM, Bob Pawley wrote:

Would it be possible for you to point me to an example??

The EXECUTE command is what you want.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

On Thursday, September 01, 2011 8:04:49 am Bob Pawley wrote:

-----Original Message-----
From: Bill Moran
Sent: Thursday, September 01, 2011 7:59 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

In response to "Bob Pawley" <rjpawley@shaw.ca>:

I want to add information to multiple columns (20 – 40) by employing a
loop. Each pass of the loop will populate one column with an array.

I have tried and I have read that variables can not be used to control
column names.

Is there a means of working around this restriction other than creating
20 to 40 hard coded statements??

You can generate dynamic SQL statements with plpgsql.

Would it be possible for you to point me to an example??

http://www.postgresql.org/docs/9.0/interactive/plpgsql-statements.html#PLPGSQL-
STATEMENTS-EXECUTING-DYN
--
Adrian Klaver
adrian.klaver@gmail.com

In response to "Bob Pawley" <rjpawley@shaw.ca>:

From: Bill Moran

In response to "Bob Pawley" <rjpawley@shaw.ca>:

I want to add information to multiple columns (20 - 40) by employing a
loop. Each pass of the loop will populate one column with an array.

I have tried and I have read that variables can not be used to control
column names.

Is there a means of working around this restriction other than creating 20
to 40 hard coded statements??

You can generate dynamic SQL statements with plpgsql.

Would it be possible for you to point me to an example??

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4

If you're not familiar with plpgsql at all, you might want to start with
this:
http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-----Original Message-----
From: Bill Moran
Sent: Thursday, September 01, 2011 8:19 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4

If you're not familiar with plpgsql at all, you might want to start with
this:
http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html

Thanks for the suggestion.

Following is my interpretation of what I have read.

I am getting an error -- "column "1" does not exist"

Could someone point to what I am doing wrong?

Bob

Select 2 into point_array ;
Select "1" into column ;

Loop

Execute 'Update library.compare Set'
|| quote_ident (column[point_array])
|| '=
(select st_distance (st_geometryn(public.similar.the_geom, 1),
(st_geometryn(public.similar.the_geom, point_array)))/
public.similar.prime
from public.similar
where public.similar.sight_description = ''H_Line'')'
-- || newvalue
|| 'from public.import_process_transfer'
|| 'where library.compare.process_id =
public.import_process_transfer.process_id';
-- || quote_literal();

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 02/09/2011 18:33, Bob Pawley wrote:

-----Original Message-----

From: Bill Moran

Sent: Thursday, September 01, 2011 8:19 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4

If you're not familiar with plpgsql at all, you might want to start with
this:
http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html

Thanks for the suggestion.

Following is my interpretation of what I have read.

I am getting an error -- "column "1" does not exist"

Could someone point to what I am doing wrong?

Bob

Select 2 into point_array ;
Select "1" into column ;

Hi Bob,

I think it is the double-quotes around the 1; just leave them out to get
a literal integer 1:

select 1 into column;

If I understand correctly, the double-quotes make Postgres look for a
column named "1".

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

-----Original Message-----
From: Raymond O'Donnell
Sent: Friday, September 02, 2011 10:38 AM
To: Bob Pawley
Cc: Bill Moran ; Postgresql
Subject: Re: [GENERAL] Variable column name

On 02/09/2011 18:33, Bob Pawley wrote:

-----Original Message-----

From: Bill Moran

Sent: Thursday, September 01, 2011 8:19 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

http://www.postgresql.org/docs/9.0/static/plpgsql-statements.html
Section 39.5.4

If you're not familiar with plpgsql at all, you might want to start with
this:
http://www.postgresql.org/docs/9.0/static/plpgsql-structure.html

Thanks for the suggestion.

Following is my interpretation of what I have read.

I am getting an error -- "column "1" does not exist"

Could someone point to what I am doing wrong?

Bob

Select 2 into point_array ;
Select "1" into column ;

Hi Bob,

I think it is the double-quotes around the 1; just leave them out to get
a literal integer 1:

select 1 into column;

If I understand correctly, the double-quotes make Postgres look for a
column named "1".

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Ray
I've named columns 1 through 10 so that it will be easy to determine the
next column in the loop.
When I use the following it works well.

Update library.compare
Set "1"[2] =
(select st_distance (st.............................

In response to "Bob Pawley" <rjpawley@shaw.ca>:

I am getting an error -- "column "1" does not exist"

<snip>

Select "1" into column ;

Where are you selecting "1" from? This query has no FROM clause, so of
course the column doesn't exist.

The previous query, "SELECT 2 INTO point_array" is going to put the
integer value 2 into the variable point_array, which I'm guessing is
not what you want either.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-----Original Message-----
From: Bill Moran
Sent: Friday, September 02, 2011 10:53 AM
To: Bob Pawley
Cc: Postgresql
Subject: Re: [GENERAL] Variable column name

In response to "Bob Pawley" <rjpawley@shaw.ca>:

I am getting an error -- "column "1" does not exist"

<snip>

Select "1" into column ;

Where are you selecting "1" from? This query has no FROM clause, so of
course the column doesn't exist.

The previous query, "SELECT 2 INTO point_array" is going to put the
integer value 2 into the variable point_array, which I'm guessing is
not what you want either.

Well, actually that is what I am attempting.

I added the from clause and that seems to be acceptable for the column
identification.

What I am trying to accomplish is to collect distance information between
numerous geometries (in this case 8) at the first spatial location and build
an array in column 1, one array point at a time.

Then the loop moves to the next location, establishes the geometries and
updates the column 2 array with these distances.

It seems to work when I hard code the column name and array point, so I was
hoping to make it work through a loop using variables for column and array
point.

Does this make sense??

Bob

On Sep 2, 2011, at 2:31 PM, Bob Pawley wrote:

It seems to work when I hard code the column name and array point, so I was hoping to make it work through a loop using variables for column and array point.

Does this make sense??

Building queries this way is tedious & error prone; that's just the way it is.

Put the command into a variable, then raise a notice with that variable, then execute it. That way, when you get a failure, you just copy the failed SQL from the notice into an editor, tweak it until it works, then adjust your code accordingly to produce the corrected query.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice