Re: 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 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)

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