Postgres Pro
Downloads
Home   >   mailing lists

Thread: dynamic table naming in function

dynamic table naming in function

From
Matthew Peter
Date:
Out of curiosity, I was wondering if it is possible to
use dynamic table names in a function? AND whether or
not you can test a value prior to insert to see
whether or not you want to update that column. Where
you could pass in the name of the table for it use ANY
name passed in rather than statically defining it.

I'll try an example of what I'm thinking;

CREATE OR REPLACE FUNCTION updatefoo(tbl_prefix
varchar, data  mydata, myid bigint) RETURNS
boolean AS $$
DECLARE
BEGIN
    update ${tbl_prefix}rest_of_table_name  set
    f1 = mydata.f1
    IF LEN mydata.f2 THEN   -- this possible? to
update f2 ONLY if it contains data?
        ,f2 = mydata.f2
    END IF;
    WHERE id  = myid;

     IF NOT FOUND THEN
         return false;
     END IF;
     return true;
END
$$ LANGUAGE plpgsql;



__________________________________
Yahoo! FareChase: Search multiple travel sites in one click.
http://farechase.yahoo.com

Re: dynamic table naming in function

From
Bruno Wolff III
Date:
On Sat, Oct 29, 2005 at 10:45:21 -0700,
  Matthew Peter <survivedsushi@yahoo.com> wrote:
> Out of curiosity, I was wondering if it is possible to
> use dynamic table names in a function? AND whether or
> not you can test a value prior to insert to see
> whether or not you want to update that column. Where
> you could pass in the name of the table for it use ANY
> name passed in rather than statically defining it.

You can use the EXECUTE statement to do this kind of thing.

Re: dynamic table naming in function

From
Tom Lane
Date:
Matthew Peter <survivedsushi@yahoo.com> writes:
> Out of curiosity, I was wondering if it is possible to
> use dynamic table names in a function?

In plpgsql, you can do this by building dynamic query strings and
EXECUTE'ing them.  Most of the other PLs don't cache query plans
at all, and so all queries are effectively EXECUTE'd and there's
no issue.

You'll want to read up on quote_literal and quote_ident to help
you in building correct query strings.

            regards, tom lane

Re: dynamic table naming in function

From
Matthew Peter
Date:
Thanks for pointing me in the right direction. I read
about EXECUTE in the docs now.


> Most of the other PLs don't cache query plans
> at all, and so all queries are effectively EXECUTE'd
> and there's no issue.

I'm not sure what you mean...

Is there a more suitable LANGUAGE declaration you
would recommend? If possible, I would also like to
return results from SELECT statments from the function
but EXECUTE doesn't return any results. I'm not tied
to plpgsql, but would like to make a couple dynamic
functions with whatever way is simplest or best tool
for the job.



--- Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Matthew Peter <survivedsushi@yahoo.com> writes:
> > Out of curiosity, I was wondering if it is
> possible to
> > use dynamic table names in a function?
>
> In plpgsql, you can do this by building dynamic
> query strings and
> EXECUTE'ing them.  Most of the other PLs don't cache
> query plans
> at all, and so all queries are effectively EXECUTE'd
> and there's
> no issue.
>
> You'll want to read up on quote_literal and
> quote_ident to help
> you in building correct query strings.
>
>             regards, tom lane
>





__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com