pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedComputed table name in function
5+ messages / 4 participants
[nested] [flat]
* Computed table name in function
@ 2007-10-10 17:59 Ray Madigan <ray@madigans.org>
2007-10-10 18:10 ` Re: Computed table name in function Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Ray Madigan @ 2007-10-10 17:59 UTC (permalink / raw)
To: pgsql-sql
I have a problem that I don't know where to look to understand the problem.
I have a function that I first select to get a table name followed by
another select into on that table name. If someone could tell me where to
look to solve this problem I would appreciate it.
It is something like
DECLARE rec1 Record;
rec2 Record;
SELECT aName INTO rec1 FROM tableA WHERE new.xxx = ?;
IF FOUND THEN
SELECT * INTO rec2 FROM rec1.aName where new.xxx = ?;
Thanks
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Computed table name in function
2007-10-10 17:59 Computed table name in function Ray Madigan <ray@madigans.org>
@ 2007-10-10 18:10 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-10 18:19 ` Re: Computed table name in function Ray Madigan <ray@madigans.org>
0 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2007-10-10 18:10 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: pgsql-sql
On 10/10/07, Ray Madigan <ray@madigans.org> wrote:
> I have a problem that I don't know where to look to understand the problem.
>
> I have a function that I first select to get a table name followed by
> another select into on that table name. If someone could tell me where to
> look to solve this problem I would appreciate it.
>
> It is something like
>
> DECLARE rec1 Record;
> rec2 Record;
>
> SELECT aName INTO rec1 FROM tableA WHERE new.xxx = ?;
>
> IF FOUND THEN
>
> SELECT * INTO rec2 FROM rec1.aName where new.xxx = ?;
You have to build your query as a string then use execute on it.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Computed table name in function
2007-10-10 17:59 Computed table name in function Ray Madigan <ray@madigans.org>
2007-10-10 18:10 ` Re: Computed table name in function Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-10-10 18:19 ` Ray Madigan <ray@madigans.org>
2007-10-10 18:50 ` Re: Computed table name in function Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
2007-10-10 18:52 ` Re: Computed table name in function Richard Huxton <dev@archonet.com>
0 siblings, 2 replies; 5+ messages in thread
From: Ray Madigan @ 2007-10-10 18:19 UTC (permalink / raw)
To: pgsql-sql
I thought that the documentation said I couldn't use EXECUTE on a SELECT
INTO?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Scott Marlowe
Sent: Wednesday, October 10, 2007 11:10 AM
To: Ray Madigan
Cc: Pgsql-Sql
Subject: Re: [SQL] Computed table name in function
On 10/10/07, Ray Madigan <ray@madigans.org> wrote:
> I have a problem that I don't know where to look to understand the
problem.
>
> I have a function that I first select to get a table name followed by
> another select into on that table name. If someone could tell me where to
> look to solve this problem I would appreciate it.
>
> It is something like
>
> DECLARE rec1 Record;
> rec2 Record;
>
> SELECT aName INTO rec1 FROM tableA WHERE new.xxx = ?;
>
> IF FOUND THEN
>
> SELECT * INTO rec2 FROM rec1.aName where new.xxx = ?;
You have to build your query as a string then use execute on it.
---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at
http://www.postgresql.org/about/donate
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Computed table name in function
2007-10-10 17:59 Computed table name in function Ray Madigan <ray@madigans.org>
2007-10-10 18:10 ` Re: Computed table name in function Scott Marlowe <scott.marlowe@gmail.com>
2007-10-10 18:19 ` Re: Computed table name in function Ray Madigan <ray@madigans.org>
@ 2007-10-10 18:50 ` Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
1 sibling, 0 replies; 5+ messages in thread
From: Daniel Drotos @ 2007-10-10 18:50 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: pgsql-sql
On Wed, 10 Oct 2007, Ray Madigan wrote:
> I thought that the documentation said I couldn't use EXECUTE on a SELECT
> INTO?
Try "for record_var in select ..." Look for 36.7.4 Looping Through
Query Results in the docs.
Daniel
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Computed table name in function
2007-10-10 17:59 Computed table name in function Ray Madigan <ray@madigans.org>
2007-10-10 18:10 ` Re: Computed table name in function Scott Marlowe <scott.marlowe@gmail.com>
2007-10-10 18:19 ` Re: Computed table name in function Ray Madigan <ray@madigans.org>
@ 2007-10-10 18:52 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-10-10 18:52 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: pgsql-sql
Ray Madigan wrote:
> I thought that the documentation said I couldn't use EXECUTE on a SELECT
> INTO?
"37.6.5. Executing Dynamic Commands
Oftentimes you will want to generate dynamic commands inside your
PL/pgSQL functions, that is, commands that will involve different tables
or different data types each time they are executed. PL/pgSQL's normal
attempts to cache plans for commands will not work in such scenarios. To
handle this sort of problem, the EXECUTE statement is provided:
EXECUTE command-string [ INTO [STRICT] target ];
where command-string is an expression yielding a string (of type text)
containing the command to be executed and target is a record variable,
row variable, or a comma-separated list of simple variables and
record/row fields.
"
There's also a FOR..IN EXECUTE loop too
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-10-10 18:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-10 17:59 Computed table name in function Ray Madigan <ray@madigans.org>
2007-10-10 18:10 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-10-10 18:19 ` Ray Madigan <ray@madigans.org>
2007-10-10 18:50 ` Daniel Drotos <drdani@mazsola.iit.uni-miskolc.hu>
2007-10-10 18:52 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox