Re: variable use for selects

Hi Tom,

Thanks for the input, I should do more reading up on database efficiency.

I had thought I would be gaining performance by doing this as I will be looking at potentially 1.5 million new rows
eachweek. 

Regards,
Danni


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, 6 July 2009 3:15 PM
To: Allen, Danni
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] variable use for selects

"Allen, Danni" <Danni.Allen@Avnet.com> writes:
> I'm doing the following:

> myseriestable := myleagueid || '_' || myleaguename || '_series';
> myseriesid := (select seriesid from myseriestable order by seriesid desc limit 1);

You can't use a variable where a table name is required.

If you are absolutely intent on doing this, you could construct the
whole query (not just the table name) as a string and EXECUTE it.
However, my advice would be to rethink your database schema.  Anytime
you have N tables with the same layout, you should consider whether
they oughtn't be one table with an extra key column (or maybe two
columns, in this example).  What you're doing is basically simulating
the extra key columns by hand, and it is generally *not* any kind of
efficiency win to do that.

            regards, tom lane