Обсуждение: variable use for selects
Hi,
I am new to sql and even newer to postgres. I’m sure this is very basic and that is why I cannot find anyone else running into the same problem. Hopefully this is trivial and is just a matter of knowing what is and isn’t legal syntax wise…
I’m doing the following:
myleaguename := (select league from data_league where leagueid = myleagueid);
-- BEGIN Create the league series table
myseriestable := myleagueid || '_' || myleaguename || '_series';
myseriestableexec := 'CREATE TABLE ' || myseriestable || '(seriesid integer NOT NULL, seriesname character varying(5) NOT NULL, divisionid integer NOT NULL, leagueid integer NOT NULL, CONSTRAINT bask_series_pkey PRIMARY KEY (seriesid), CONSTRAINT bask_series_leagueid_fkey FOREIGN KEY (leagueid) REFERENCES data_league (leagueid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION) WITH (OIDS=FALSE); ALTER TABLE ' || myseriestable || ' OWNER TO postgres;';
execute myseriestableexec;
-- END Create the league series table
myseriesid := (select seriesid from myseriestable order by seriesid desc limit 1);
ERROR: syntax error at or near "$1"
LINE 1: SELECT (select seriesid from $1 order by seriesid desc li...
^
The carat is actually pointing at the variable itself. I’m wondering if it is possible to assign the result to that variable (myseriesid) as is or do I have to build the string and execute it separately, much like the creation of the table above? Postgres won’t expand it for me in the line as is? Is that correct?
Thanks,
Danni
"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
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