Thread: from string to table...?

let's say you have the name of a table in a string.

    'mytable'

you also have field names, such as

    'lookupfield' 'valuefield'

which would be assembled like this

    select valuefield from mytable where lookupfield = '?'

is there a way to write sql/plpgsql that'll take a varchar
argument (table and field names) and be able to turn it into the
appropriate query?

i can see pg_class contains table info and pg_attribute has info
for the fields ... is there a way (oids maybe?) to direct
postgres to the right table for a search, given the name of the
table in a varchar string? (and if so, how? :)

--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

will trillich wrote:
>
> let's say you have the name of a table in a string.
>
>         'mytable'
>
> you also have field names, such as
>
>         'lookupfield' 'valuefield'
>
> which would be assembled like this
>
>         select valuefield from mytable where lookupfield = '?'
>
> is there a way to write sql/plpgsql that'll take a varchar
> argument (table and field names) and be able to turn it into the
> appropriate query?

You can use the "EXECUTE" statement in plpgsql (v7.1) to dynamically
build a query. The catch is you can't return rows from a function. You
could use it to build a view and then query that.

Probably easiest to do in the application layer though.

- Richard Huxton

On Fri, 23 Mar 2001, will trillich wrote:

> let's say you have the name of a table in a string.
>
>     'mytable'
>
> you also have field names, such as
>
>     'lookupfield' 'valuefield'
>
> which would be assembled like this
>
>     select valuefield from mytable where lookupfield = '?'
>
> is there a way to write sql/plpgsql that'll take a varchar
> argument (table and field names) and be able to turn it into the
> appropriate query?
>
> i can see pg_class contains table info and pg_attribute has info
> for the fields ... is there a way (oids maybe?) to direct
> postgres to the right table for a search, given the name of the
> table in a varchar string? (and if so, how? :)

Well, under 7.1, you can use EXECUTE to build query strings inside
the procedure and execute them, but you may run into return value
problems if you're trying to execute that select and return the
value.

On Fri, Mar 23, 2001 at 07:53:39AM +0000, Richard Huxton wrote:
> will trillich wrote:
> > is there a way to write sql/plpgsql that'll take a varchar
> > argument (table and field names) and be able to turn it into the
> > appropriate query?
>
> You can use the "EXECUTE" statement in plpgsql (v7.1) to dynamically
> build a query. The catch is you can't return rows from a function. You
> could use it to build a view and then query that.
>
> Probably easiest to do in the application layer though.

<blank stare> um, what's "application layer" mean? :)

--
will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

will trillich wrote:
>
> <blank stare> um, what's "application layer" mean? :)
>
sorry - I mean customise queries in the application itself. I tend to
build a database abstraction layer which does all this stuff for me.

- Richard Huxton