Thread: Variable column name
Hi
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??
Bob
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 columnwith 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 usingvariables 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 accordinglyto produce the corrected query. -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice