Обсуждение: To pass schemaname as a function parameter
Hi,
I wrote a function in plpgsql, to dispaly the student list.
In a Database all schemas contains studentlist table. so I wrote the function with schemaname as a parameter(text data type). My code is like
CREATE FUNCTION disp_fn(schemaname text) AS $$ BEGIN SELECT * FROM schemaname.studentlist; END; $$ LANGUAGE plpgsql;
In the above function schemaname varaible does not taken.
Is there any way to pass schemaname as argument? Any idea would be much appreciated.
Thanks in Advance.
Regards
Softlinne
In response to Kalai R : > Hi, > > I wrote a function in plpgsql, to dispaly the student list. > > In a Database all schemas contains studentlist table. so I wrote the function with schemaname as a parameter(text datatype). My code is like > > CREATE FUNCTION disp_fn(schemaname text) AS $$ > BEGIN > SELECT * FROM schemaname.studentlist; > END; > $$ LANGUAGE plpgsql; Not possible in this way, use EXECUTE: execute 'select * from ' || schemaname || '.studentlist'; (it is a dynamic SQL, you haven't a fix tablename) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
On Thu, Sep 3, 2009 at 7:37 AM, A. Kretschmer<andreas.kretschmer@schollglas.com> wrote: > In response to Kalai R : >> Hi, >> >> I wrote a function in plpgsql, to dispaly the student list. >> >> In a Database all schemas contains studentlist table. so I wrote the function with schemaname as a parameter(text datatype). My code is like >> >> CREATE FUNCTION disp_fn(schemaname text) AS $$ >> BEGIN >> SELECT * FROM schemaname.studentlist; >> END; >> $$ LANGUAGE plpgsql; > > Not possible in this way, use EXECUTE: > > execute 'select * from ' || schemaname || '.studentlist'; > > > (it is a dynamic SQL, you haven't a fix tablename) also (IMO preferred), execute 'set search_path = public, ' || schemaname; SELECT * FROM studentlist; merlin