Thread: select into

Hi guys,<br /><br />   Am new to sql scripting so this might be a stupid question. Am getting an error while trying to
dothe following<br />  <br />SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;<br /><br />where
svc_data_recis defined as record and svc_tbl_name is a varchar that holds the name of a table  and sub_id is another
varchar.the error message is<br /><br />   ERROR:  syntax error at or near "$1" at character 17<br />   QUERY:  SELECT 
*from  $1  where 'uid' =  $2<br />   CONTEXT:  PL/pgSQL function "foo" line 132 at select into variables<br />   LINE
1:SELECT  * from  $1  where 'uid' =  $2<br /><br />   where am i going wrong? it seems that the variables are not being
substitutedwith their values correctly.<br /><br />   Thank you,<br /><br /><p><font color="666666"><font size="2"><hr
size="1"/>Sponsored Link</font></font><br /><br />Mortgage rates near 39yr lows. $420,000 Mortgage for $1,399/mo - <a
href="http://www.lowermybills.com/lre/index.jsp?sourceid=lmb-9132-16414&moid=4116">Calculatenew house payment</a> 

am  Wed, dem 22.11.2006, um  0:28:15 -0800 mailte Mulham freshcode folgendes:
> Hi guys,
> 
>    Am new to sql scripting so this might be a stupid question. Am getting an
> error while trying to do the following
>  
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> 
> where svc_data_rec is defined as record and svc_tbl_name is a varchar that
> holds the name of a table  and sub_id is another varchar. the error message is

You should rewrite your plpgsql-function. You can't handle with
string-vars in this way, you must create a string with your complete sql
and EXECUTE this string.

Read
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

> Hi guys,
> 
>    Am new to sql scripting so this might be a stupid question. Am getting an error while trying
> to do the following
>   
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> 
> where svc_data_rec is defined as record and svc_tbl_name is a varchar that holds the name of a
> table  and sub_id is another varchar. the error message is
> 
>    ERROR:  syntax error at or near "$1" at character 17
>    QUERY:  SELECT  * from  $1  where 'uid' =  $2
>    CONTEXT:  PL/pgSQL function "foo" line 132 at select into variables
>    LINE 1: SELECT  * from  $1  where 'uid' =  $2
> 
>    where am i going wrong? it seems that the variables are not being substituted with their
> values correctly.

Notice:
http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html
and particularly this example:
http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014
Regards,

Richard Broersma Jr.

Richard Broersma Jr <rabroersma@yahoo.com> schrieb:

> > Hi guys,
> > 
> >    Am new to sql scripting so this might be a stupid question. Am getting an error while trying
> > to do the following
> >   
> > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> > 
> > where svc_data_rec is defined as record and svc_tbl_name is a varchar that holds the name of a
> > table  and sub_id is another varchar. the error message is
> > 
> >    ERROR:  syntax error at or near "$1" at character 17
> >    QUERY:  SELECT  * from  $1  where 'uid' =  $2
> >    CONTEXT:  PL/pgSQL function "foo" line 132 at select into variables
> >    LINE 1: SELECT  * from  $1  where 'uid' =  $2
> > 
> >    where am i going wrong? it seems that the variables are not being substituted with their
> > values correctly.
> 
> Notice:
> http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html
> and particularly this example:
> http://www.postgresql.org/docs/8.2/interactive/sql-selectinto.html#AEN54014
> Regards,

Richard, the plain SQL "INSERT INTO <table>" is an other thing as the
original problem...

He is in a PL/pgsql - function and he should rewrite this to use EXECUTE
... INTO


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Mulham freshcode <mulhamcode@yahoo.com> writes:
>    Am new to sql scripting so this might be a stupid question. Am getting an error while trying to do the following
> SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;

The error message shows that this is getting rewritten into

>    QUERY:  SELECT  * from  $1  where 'uid' =  $2

so the problem is that you are using svc_tbl_name as a plpgsql variable,
and plpgsql is not bright enough to realize that it shouldn't substitute
the variable value at this particular spot in the query.  You need to
change the variable name to something that won't conflict.  In general,
don't use plpgsql variables that are named the same as any SQL tables or
columns you need to mention in the function.
        regards, tom lane

--- Andreas Kretschmer <akretschmer@spamfence.net> wrote: 
> Richard, the plain SQL "INSERT INTO <table>" is an other thing as the
> original problem...
> 
> He is in a PL/pgsql - function and he should rewrite this to use EXECUTE
> ... INTO
> 

I see,  I am not even a novice yet when it comes PL/pgsql. Thanks for the clarification.

Regards,

Regards,

Richard Broersma Jr.

Hi,<br />    Thanks very much for all the suggestions. Like Andreas said i have to use EXECUTE to do this. That was my
guesstoo but i was putting the INTO cluase into the string before executing it. This is a step forward. The problem now
isgetting the into to work with a record. Am using a RECORD variable after declaring it in the INTO clause. pg is
complaining<br /><br />   ERROR:  record "svc_data_rec" is not assigned yet<br />   DETAIL:  The tuple structure of a
not-yet-assignedrecord is indeterminate.<br />   CONTEXT:  PL/pgSQL function "foo" line 130 at execute statement<br
/><br/>   I read in the docs the following, "The INTO clause specifies where the results of a SELECT command should be
assigned.If a row or variable list is provided, it must exactly match the structure of the results produced by the
SELECT(when a record variable is used, it will configure itself to match the result's structure automatically)." That i
taketo mean that the above is ok. But it ain't.<br /><br />Thanks again for your guys help...<br /><br /><br
/><b><i>"A.Kretschmer" <andreas.kretschmer@schollglas.com></i></b> wrote:<blockquote class="replbq"
style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> am Wed, dem 22.11.2006, um
0:28:15-0800 mailte Mulham freshcode folgendes:<br />> Hi guys,<br />> <br />> Am new to sql scripting so this
mightbe a stupid question. Am getting an<br />> error while trying to do the following<br />> <br />> SELECT
INTOsvc_data_rec * from svc_tbl_name where 'uid' = sub_id;<br />> <br />> where svc_data_rec is defined as record
andsvc_tbl_name is a varchar that<br />> holds the name of a table and sub_id is another varchar. the error message
is<br/><br />You should rewrite your plpgsql-function. You can't handle with<br />string-vars in this way, you must
createa string with your complete sql<br />and EXECUTE this string.<br /><br />Read<br
/>http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN<br/><br /><br
/>Andreas<br/>-- <br />Andreas Kretschmer<br />Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)<br
/>GnuPG-ID:0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net<br /><br />---------------------------(end of
broadcast)---------------------------<br/>TIP 1: if posting/reading through Usenet, please send an appropriate<br />
subscribe-nomailcommand to majordomo@postgresql.org so that your<br /> message can get through to the mailing list
cleanly<br/></blockquote><br /><p><hr size="1" />Access over 1 million songs - <a
href="http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/*http://music.yahoo.com/unlimited/">Yahoo!Music
Unlimited.</a>

On Thursday 23 November 2006 09:19 am, Mulham freshcode wrote:
> Hi,
>     Thanks very much for all the suggestions. Like Andreas said i have to
> use EXECUTE to do this. That was my guess too but i was putting the INTO
> cluase into the string before executing it. This is a step forward. The
> problem now is getting the into to work with a record. Am using a RECORD
> variable after declaring it in the INTO clause. pg is complaining
>
>    ERROR:  record "svc_data_rec" is not assigned yet
>    DETAIL:  The tuple structure of a not-yet-assigned record is
> indeterminate. CONTEXT:  PL/pgSQL function "foo" line 130 at execute
> statement
>
>    I read in the docs the following, "The INTO clause specifies where the
> results of a SELECT command should be assigned. If a row or variable list
> is provided, it must exactly match the structure of the results produced by
> the SELECT (when a record variable is used, it will configure itself to
> match the result's structure automatically)." That i take to mean that the
> above is ok. But it ain't.
>
> Thanks again for your guys help...
>
> "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: am  Wed, dem 
22.11.2006, um  0:28:15 -0800 mailte Mulham freshcode folgendes:
> > Hi guys,
> >
> >    Am new to sql scripting so this might be a stupid question. Am getting
> > an error while trying to do the following
> >
> > SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;
> >
> > where svc_data_rec is defined as record and svc_tbl_name is a varchar
> > that holds the name of a table  and sub_id is another varchar. the error
> > message is
>
> You should rewrite your plpgsql-function. You can't handle with
> string-vars in this way, you must create a string with your complete sql
> and EXECUTE this string.
>
> Read
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG
>SQL-STATEMENTS-EXECUTING-DYN
>
>
> Andreas

My guess is that the error message is correct, the svc_data_rec has not had 
any values assigned to it. In other words the EXECUTE statement is not 
working the way you think it is. Could you post the EXECUTE string?

-- 
Adrian Klaver    
aklaver@comcast.net

Hello Adrian,<br /><br />Here is my execute string,<br /><br />sql_str1 = 'select * from ' || svc_tbl_name || ' where
uid= ' || sub_id ;<br />execute sql_str1 into svc_data_rec ;<br /><br />svc_data_rec is a RECORD, which is supposed to
bedynamic. If I need to define the structure of the record then there will be no point in using it since I need
somethingto change according the structure the table, which i don't know in advance...<br /><br />Thank you,<br /><br
/>Mustafa...<br/><br /><br /><b><i>Adrian Klaver <aklaver@comcast.net></i></b> wrote:<blockquote class="replbq"
style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> On Thursday 23 November 2006
09:19am, Mulham freshcode wrote:<br />> Hi,<br />> Thanks very much for all the suggestions. Like Andreas said i
haveto<br />> use EXECUTE to do this. That was my guess too but i was putting the INTO<br />> cluase into the
stringbefore executing it. This is a step forward. The<br />> problem now is getting the into to work with a record.
Amusing a RECORD<br />> variable after declaring it in the INTO clause. pg is complaining<br />><br />> ERROR:
record"svc_data_rec" is not assigned yet<br />> DETAIL: The tuple structure of a not-yet-assigned record is<br
/>>indeterminate. CONTEXT: PL/pgSQL function "foo" line 130 at execute<br />> statement<br />><br />> I
readin the docs the following, "The INTO clause specifies where the<br />> results of a SELECT command should be
assigned.If a row or variable list<br />> is provided, it must exactly match the structure of the results produced
by<br/>> the SELECT (when a record variable is used, it will configure itself to<br />> match the result's
structureautomatically)." That i take to mean that the<br />> above is ok. But it ain't.<br />><br />> Thanks
againfor your guys help...<br />><br />> "A. Kretschmer"  wrote: am Wed, dem <br />22.11.2006, um 0:28:15 -0800
mailteMulham freshcode folgendes:<br />> > Hi guys,<br />> ><br />> > Am new to sql scripting so this
mightbe a stupid question. Am getting<br />> > an error while trying to do the following<br />> ><br />>
>SELECT INTO svc_data_rec * from svc_tbl_name where 'uid' = sub_id;<br />> ><br />> > where svc_data_rec
isdefined as record and svc_tbl_name is a varchar<br />> > that holds the name of a table and sub_id is another
varchar.the error<br />> > message is<br />><br />> You should rewrite your plpgsql-function. You can't
handlewith<br />> string-vars in this way, you must create a string with your complete sql<br />> and EXECUTE
thisstring.<br />><br />> Read<br />>
http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPG<br/>>SQL-STATEMENTS-EXECUTING-DYN<br
/>><br/>><br />> Andreas<br /><br />My guess is that the error message is correct, the svc_data_rec has not
had<br />any values assigned to it. In other words the EXECUTE statement is not <br />working the way you think it is.
Couldyou post the EXECUTE string?<br /><br />-- <br />Adrian Klaver <br />aklaver@comcast.net<br /></blockquote><br
/><p><hrsize="1" />Want to start your own business? Learn how on <a
href="http://us.rd.yahoo.com/evt=41244/*http://smallbusiness.yahoo.com/r-index">Yahoo!Small Business.</a> 

Mulham freshcode <mulhamcode@yahoo.com> writes:
> execute sql_str1 into svc_data_rec ;

> svc_data_rec is a RECORD, which is supposed to be dynamic.

This should work --- in PG 8.1 or later.  In older versions you'd have
to fool around with a FOR ... IN EXECUTE ... loop.
        regards, tom lane

Hi Tom,<br /><br /> Thanks for the help. Am using version 8.0 and it seems like RECORD is not that dynamic still. I
triedwith the FOR ... IN EXECUTE ... LOOP and it does the trick. But am still finding it hard to move forward with
this.I have the name of table field in a varchar variable that i got from information_schema.columns and I have the
recordsvariable that stores the  contains the data from that table. Usually I'd do something like data_rec.col_name to
extractthe data from the record but now I don't know the name per se. how can i say something like data_rec[col_name] 
wherecol_name is a variable that has the actual column name. I found no examples in the docs that explain this. Can it
bedone in version 8.0.1?<br /><br />I find variable substitution kind of confusing. I mean why is there no way of
sayingexplicitly replace this variable with its content before executing the statement?<br /><br /> Sorry for the long
question,<br/> and thanks again for the help<br /><br /> Mustafa...<br /><br /><br /><b><i>Tom Lane
<tgl@sss.pgh.pa.us></i></b>wrote:<blockquote class="replbq" style="border-left: 2px solid rgb(16, 16, 255);
margin-left:5px; padding-left: 5px;"> Mulham freshcode  writes:<br />> execute sql_str1 into svc_data_rec ;<br /><br
/>>svc_data_rec is a RECORD, which is supposed to be dynamic.<br /><br />This should work --- in PG 8.1 or later. In
olderversions you'd have<br />to fool around with a FOR ... IN EXECUTE ... loop.<br /><br /> regards, tom lane<br /><br
/>---------------------------(endof broadcast)---------------------------<br />TIP 3: Have you checked our extensive
FAQ?<br/><br /> http://www.postgresql.org/docs/faq<br /></blockquote><br /><p><hr size="1" />Everyone is raving about
<ahref="http://us.rd.yahoo.com/evt=45083/*http://advision.webevents.yahoo.com/mailbeta">the all-new Yahoo! Mail
beta.</a>

On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
> Mulham freshcode <mulhamcode@yahoo.com> writes:
> > execute sql_str1 into svc_data_rec ;
> >
> > svc_data_rec is a RECORD, which is supposed to be dynamic.
>
> This should work --- in PG 8.1 or later.  In older versions you'd have
> to fool around with a FOR ... IN EXECUTE ... loop.
>
>             regards, tom lane
The documentation for pl/pgsql  in 8.1 and higher says different. 

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
" SELECT INTO is not currently supported within EXECUTE."

The change is reflected in the HISTORY text though. How is the best way to get 
a change made to the documentation?
-- 
Adrian Klaver    
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> schrieb:

> On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
> > Mulham freshcode <mulhamcode@yahoo.com> writes:
> > > execute sql_str1 into svc_data_rec ;
> > >
> > > svc_data_rec is a RECORD, which is supposed to be dynamic.
> >
> > This should work --- in PG 8.1 or later.  In older versions you'd have
> > to fool around with a FOR ... IN EXECUTE ... loop.
> >
> >             regards, tom lane
> The documentation for pl/pgsql  in 8.1 and higher says different. 
> 
> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
> " SELECT INTO is not currently supported within EXECUTE."

Thats right. The solution with 8.1 or later is:

EXECUTE .... INTO var, and _not_ SELECT INTO var.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On Friday 24 November 2006 06:55 am, Andreas Kretschmer wrote:
> Adrian Klaver <aklaver@comcast.net> schrieb:
> > On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
> > > Mulham freshcode <mulhamcode@yahoo.com> writes:
> > > > execute sql_str1 into svc_data_rec ;
> > > >
> > > > svc_data_rec is a RECORD, which is supposed to be dynamic.
> > >
> > > This should work --- in PG 8.1 or later.  In older versions you'd have
> > > to fool around with a FOR ... IN EXECUTE ... loop.
> > >
> > >             regards, tom lane
> >
> > The documentation for pl/pgsql  in 8.1 and higher says different.
> >
> > http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PL
> >PGSQL-STATEMENTS-EXECUTING-DYN " SELECT INTO is not currently supported
> > within EXECUTE."
>
> Thats right. The solution with 8.1 or later is:
>
> EXECUTE .... INTO var, and _not_ SELECT INTO var.
>
>
> Andreas
Thanks for the information. Somewhere along the line I missed the distinction.
-- 
Adrian Klaver    
aklaver@comcast.net

On Thursday 23 November 2006 10:54 pm, Mulham freshcode wrote:
> Hi Tom,
>
>  Thanks for the help. Am using version 8.0 and it seems like RECORD is not
> that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP and it
> does the trick. But am still finding it hard to move forward with this. I
> have the name of table field in a varchar variable that i got from
> information_schema.columns and I have the records variable that stores the 
> contains the data from that table. Usually I'd do something like
> data_rec.col_name to extract the data from the record but now I don't know
> the name per se. how can i say something like data_rec[col_name]  where
> col_name is a variable that has the actual column name. I found no examples
> in the docs that explain this. Can it be done in version 8.0.1?
>
> I find variable substitution kind of confusing. I mean why is there no way
> of saying explicitly replace this variable with its content before
> executing the statement?
>
>  Sorry for the long question,
>  and thanks again for the help
>
>  Mustafa...
>
> Tom Lane <tgl@sss.pgh.pa.us> wrote: Mulham freshcode  writes:
> > execute sql_str1 into svc_data_rec ;
> >
> > svc_data_rec is a RECORD, which is supposed to be dynamic.
>
> This should work --- in PG 8.1 or later.  In older versions you'd have
> to fool around with a FOR ... IN EXECUTE ... loop.
>
>    regards, tom lane
>
I am  trying to sort this out. Are you trying to find the data for a single 
field from each table, or for some set of fields?. If you are looking for 
data from a single field couldn't you dispense with the RECORD variable and 
just build a query of the form SELECT col_name FROM tbl_name. If you want to 
go through a set of fields then it would involve some nested loops.

-- 
Adrian Klaver    
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:
> On Thursday 23 November 2006 08:45 pm, Tom Lane wrote:
>> This should work --- in PG 8.1 or later.

> The documentation for pl/pgsql  in 8.1 and higher says different. 

> http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
> " SELECT INTO is not currently supported within EXECUTE."

That says you can't doEXECUTE 'select ... into foo ...';
which is not the same thing asEXECUTE 'select ...' into foo;
        regards, tom lane

<br />Hi Adrian,<br /><br />I have number of similar tables that have different number of fields (similar in
functionality).An in my stored procedure am trying to select a row from one of these tables (that i don't know in
advance,hence the use of record) and return the data in the form of a table that has column_name:value pairs. where
columnname is that from the original table. I have no problem finding the column names but I don't know how to say
data[column_name]to get the corresponding value. Is there a way to do it in pgsql?<br /><br />here is my code so far
<br/><br />    sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ;<br />    <br />    for
svc_data_recin execute sql_str1 loop<br />    end loop;<br /><br />    -- get service_user table's column names<br
/>   for col_name in select column_name <br />            from information_schema.columns <br />            where
table_name~svc_tbl_nameloop<br />        <br />            raise notice 'Column name:%', col_name.column_name;<br
/>           raise notice 'Value: %', svc_data_rec[col_name.column_name];<br />    <br />    end loop;<br /><br />Thank
you,<br/><br />Mustafa ...<br /><br /><br /><b><i>Adrian Klaver <aklaver@comcast.net></i></b> wrote:<blockquote
class="replbq"style="border-left: 2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> On Thursday 23
November2006 10:54 pm, Mulham freshcode wrote:<br />> Hi Tom,<br />><br />> Thanks for the help. Am using
version8.0 and it seems like RECORD is not<br />> that dynamic still. I tried with the FOR ... IN EXECUTE ... LOOP
andit<br />> does the trick. But am still finding it hard to move forward with this. I<br />> have the name of
tablefield in a varchar variable that i got from<br />> information_schema.columns and I have the records variable
thatstores the <br />> contains the data from that table. Usually I'd do something like<br />> data_rec.col_name
toextract the data from the record but now I don't know<br />> the name per se. how can i say something like
data_rec[col_name]where<br />> col_name is a variable that has the actual column name. I found no examples<br />>
inthe docs that explain this. Can it be done in version 8.0.1?<br />><br />> I find variable substitution kind of
confusing.I mean why is there no way<br />> of saying explicitly replace this variable with its content before<br
/>>executing the statement?<br />><br />> Sorry for the long question,<br />> and thanks again for the
help<br/>><br />> Mustafa...<br />><br />> Tom Lane  wrote: Mulham freshcode writes:<br />> > execute
sql_str1into svc_data_rec ;<br />> ><br />> > svc_data_rec is a RECORD, which is supposed to be dynamic.<br
/>><br/>> This should work --- in PG 8.1 or later. In older versions you'd have<br />> to fool around with a
FOR... IN EXECUTE ... loop.<br />><br />> regards, tom lane<br />><br />I am trying to sort this out. Are you
tryingto find the data for a single <br />field from each table, or for some set of fields?. If you are looking for <br
/>datafrom a single field couldn't you dispense with the RECORD variable and <br />just build a query of the form
SELECTcol_name FROM tbl_name. If you want to <br />go through a set of fields then it would involve some nested
loops.<br/><br />-- <br />Adrian Klaver <br />aklaver@comcast.net<br /></blockquote><br /><p><hr size="1" />Access over
1million songs - <a
href="http://pa.yahoo.com/*http://us.rd.yahoo.com/evt=36035/*http://music.yahoo.com/unlimited/">Yahoo!Music
Unlimited.</a>

On Friday 24 November 2006 08:17 pm, Mulham freshcode wrote:
> Hi Adrian,
>
> I have number of similar tables that have different number of fields
> (similar in functionality). An in my stored procedure am trying to select a
> row from one of these tables (that i don't know in advance, hence the use
> of record) and return the data in the form of a table that has
> column_name:value pairs. where column name is that from the original table.
> I have no problem finding the column names but I don't know how to say
> data[column_name] to get the corresponding value. Is there a way to do it
> in pgsql?
>
> here is my code so far
>
>     sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' ||
> sub_id ;
>
>     for svc_data_rec in execute sql_str1 loop
>     end loop;
>
>     -- get service_user table's column names
>     for col_name in select column_name
>             from information_schema.columns
>             where table_name~svc_tbl_name loop
>
>             raise notice 'Column name:%', col_name.column_name;
>             raise notice 'Value: %', svc_data_rec[col_name.column_name];
>
>     end loop;
>
> Thank you,
>
> Mustafa ...
>
I am afraid I can't make it work either. 
-- 
Adrian Klaver    
aklaver@comcast.net

On Sunday 26 November 2006 02:45 pm, Adrian Klaver wrote:

>
> I am afraid I can't make it work either.
I could not make it work with pl/pgsql, but I did manage to come up with a 
solution using pl/pythonu.
The function is as follows-

CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS
$Body$
tbl_name=args[0]
cols=plpy.prepare("select column_name from information_schema.columns where\
table_name=$1",["text"])
clean=plpy.prepare("delete from dat_col where table_name=$1",["text"])
clean_tbl=plpy.execute(clean,[tbl_name])
ins=plpy.prepare("insert into dat_col values($1,$2,$3),["text","text","text"])
data_rs=plpy.execute('select * from '+tbl_name)
cols_rs=plpy.execute(cols,[tbl_name])
for i in range(len(data_rs)):   for j in range(len(cols_rs)):
plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],      data_rs[i][cols_rs[j]['column_name'] ]))
 
$Body$
LANGUAGE plpythonu;

For this to work I created a table dat_col(table_name text,column_name 
text,column_data text). The function deletes old data from the table before 
it is run, based on table name. Just run as dat_col_py("table name"). This 
assumes you have pl/pythonu installed.
-- 
Adrian Klaver    
aklaver@comcast.net

Hi Adrian,<br /><br />Thanks very much for your help...it is a pity you can't do this in plpgsql coz i have almost
everything else I need, and I hate to use yet another language. What does it take to add this mechanism to the
language?Is any one planning to add it? It can come it handy I bet.<br /><br />Thanks again,<br /><br />Mustafa...<br
/><br/><br /><br /><b><i>Adrian Klaver <aklaver@comcast.net></i></b> wrote:<blockquote class="replbq"
style="border-left:2px solid rgb(16, 16, 255); margin-left: 5px; padding-left: 5px;"> On Sunday 26 November 2006 02:45
pm,Adrian Klaver wrote:<br /><br />><br />> I am afraid I can't make it work either.<br />I could not make it
workwith pl/pgsql, but I did manage to come up with a <br />solution using pl/pythonu.<br />The function is as
follows-<br/><br />CREATE OR REPLACE FUNCTION dat_col_py(text) RETURNS text AS<br />$Body$<br />tbl_name=args[0]<br
/>cols=plpy.prepare("selectcolumn_name from information_schema.columns where\<br />table_name=$1",["text"])<br
/>clean=plpy.prepare("deletefrom dat_col where table_name=$1",["text"])<br
/>clean_tbl=plpy.execute(clean,[tbl_name])<br/>ins=plpy.prepare("insert into dat_col
values($1,$2,$3),["text","text","text"])<br/>data_rs=plpy.execute('select * from '+tbl_name)<br
/>cols_rs=plpy.execute(cols,[tbl_name])<br/>for i in range(len(data_rs)):<br /> for j in range(len(cols_rs)):<br />
plpy.execute(ins,(tbl_name,cols_rs[j]['column_name'],<br/> data_rs[i][cols_rs[j]['column_name'] ]))<br />$Body$<br
/>LANGUAGEplpythonu;<br /><br />For this to work I created a table dat_col(table_name text,column_name <br
/>text,column_datatext). The function deletes old data from the table before <br />it is run, based on table name. Just
runas dat_col_py("table name"). This <br />assumes you have pl/pythonu installed.<br />-- <br />Adrian Klaver <br
/>aklaver@comcast.net<br/><br />---------------------------(end of broadcast)---------------------------<br />TIP 9: In
versionsbelow 8.0, the planner will ignore your desire to<br /> choose an index scan if your joining column's datatypes
donot<br /> match<br /></blockquote><br /><p><hr size="1" />Cheap Talk? <a
href="http://us.rd.yahoo.com/mail_us/taglines/postman8/*http://us.rd.yahoo.com/evt=39663/*http://voice.yahoo.com">Check
out</a>Yahoo! Messenger's low PC-to-Phone call rates. 

On Monday 27 November 2006 06:31 pm, Mulham freshcode wrote:
> Hi Adrian,
>
> Thanks very much for your help...it is a pity you can't do this in plpgsql
> coz i have almost every thing else I need, and I hate to use yet another
> language. What does it take to add this mechanism to the language? Is any
> one planning to add it? It can come it handy I bet.
>
> Thanks again,
>
> Mustafa...
>
It may not be a shortcoming of the language, but a shortcoming in how well I 
understand it. I haven't given up hope I can make it work in plpgsql. The 
problem was I was going in circles. 
-- 
Adrian Klaver    
aklaver@comcast.net