Postgres Pro
Downloads
Home   >   mailing lists

Thread: Computed table name in function

Computed table name in function

From
"Ray Madigan"
Date:
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

Re: Computed table name in function

From
"Scott Marlowe"
Date:
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.

Re: Computed table name in function

From
"Ray Madigan"
Date:
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

Re: Computed table name in function

From
Daniel Drotos
Date:
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

Re: Computed table name in function

From
Richard Huxton
Date:
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