Re: [SQL] select into

[Adrian Klaver]

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   
[EMAIL PROTECTED]

---(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

Re: [SQL] select into

[Adrian Klaver]

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