Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group