[SQL] select into
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. Thank you, - Sponsored Link Mortgage rates near 39yr lows. $420,000 Mortgage for $1,399/mo - Calculate new house payment
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
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
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.
Re: [SQL] select into
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 ... Adrian Klaver <[EMAIL PROTECTED]> wrote: 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 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 [EMAIL PROTECTED] - Access over 1 million songs - Yahoo! Music Unlimited.
Re: [SQL] select into
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...
Adrian Klaver <[EMAIL PROTECTED]> wrote: 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
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
-
Cheap Talk? Check out Yahoo! Messenger's low PC-to-Phone call rates.
