Re: [SQL] select into
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" <[EMAIL PROTECTED]> 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#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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly - Access over 1 million songs - Yahoo! Music Unlimited.
Re: [SQL] select into
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" <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] select into
Hello Adrian, Here is my execute string, sql_str1 = 'select * from ' || svc_tbl_name || ' where uid = ' || sub_id ; execute sql_str1 into svc_data_rec ; svc_data_rec is a RECORD, which is supposed to be dynamic. If I need to define the structure of the record then there will be no point in using it since I need something to change according the structure the table, which i don't know in advance... Thank you, Mustafa... Adrian Klaver <[EMAIL PROTECTED]> wrote: 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" 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 [EMAIL PROTECTED] - Want to start your own business? Learn how on Yahoo! Small Business.
Re: [SQL] select into
Mulham freshcode <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] select into
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 <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq - Everyone is raving about the all-new Yahoo! Mail beta.
