Re: To pass schemaname as a function parameter

On Thu, Sep 3, 2009 at 7:37 AM, A.
Kretschmer<andreas(dot)kretschmer(at)schollglas(dot)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 data type). 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