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

Re: variable use for selects

From: "Allen, Danni" <Danni(dot)Allen(at)Avnet(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: variable use for selects
Date: 2009-07-06 05:20:04
Message-ID: 886E976A8FF9F542A773654109551F7B06C070D5@CMX032USRVS.AVNET.COM (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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 each week.


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

"Allen, Danni" <Danni(dot)Allen(at)Avnet(dot)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

In response to

pgsql-novice by date

Next:From: Ms swati chandeDate: 2009-07-06 05:25:36
Subject: Problem with createdb
Previous:From: Tom LaneDate: 2009-07-06 05:14:33
Subject: Re: variable use for selects

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