Re: Functions which use an argument to decide which table to read

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Jonathan Harden" <jonathan(dot)harden(at)zeninternet(dot)co(dot)uk>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Functions which use an argument to decide which table to read
Date: 2009-10-09 14:05:16
Message-ID: 7317.1255097116@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Jonathan Harden" <jonathan(dot)harden(at)zeninternet(dot)co(dot)uk> writes:
> I am trying to write a function which takes an argument and uses that
> argument to return a set of rows from a table with the name given (or
> possibly inferred) from the argument.

You'd need to construct the SELECT command as a string and then EXECUTE
it.

Your other problem is that you don't know what row type the function
is going to return, and there's no really nice way around that ---
you could try declaring it as RETURNS SETOF RECORD, but then you'd
have to have every calling query specify what type the result is,
and that's never any fun.

On the whole, this suggests to me that you've got a bad database schema
design and you need to reconsider things at that level. I think what
you're trying to do is effectively a form of EAV
(entity-attribute-value) which just does not play very nicely with SQL
databases. It's usually better to take the time up-front to nail down a
better-specified model of your data. If you can't, a SQL database may
not be the tool to be using.

regards, tom lane

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Bruce Momjian 2009-10-09 20:37:12 Re: syntax of \copy
Previous Message A. Kretschmer 2009-10-09 12:23:29 Re: Functions which use an argument to decide which table to read