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

Re: TYPE of TEMP table does not seem to get set

From: Bill MacArthur <webmaster(at)dhs-club(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: TYPE of TEMP table does not seem to get set
Date: 2012-04-16 20:29:00
Message-ID: 4F8C810C.6000408@dhs-club.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 4/16/2012 4:11 PM, Tom Lane wrote:
> Bill MacArthur<webmaster(at)dhs-club(dot)com>  writes:
>> I have a view "configurations.tp_transactions" which is a blend of many tables using INNER and LEFT JOINs. I want to populate a temp table from which I can obtain the data and programmatically write queries to operate on that data using certain PLPGSQL functions in a uniform fashion. I have several functions which all expect a single argument of the type "configurations.tp_transactions".
>
> I think you would need to create the temp table as a child of the main
> table for this to work.  As you're doing it, the rowtype of the temp
> table is independent of the main (and the fact that they happen to have
> the same columns is NOT good enough to make it acceptable to a function
> declared to take the main table's rowtype).
>
> 			regards, tom lane
>

I have tried this but no go:
network=# create temp table chl() inherits (configurations.tp_transactions);
ERROR:  inherited relation "tp_transactions" is not a table

This does not work either:
network=> CREATE TEMP TABLE myt OF configurations.tp_transactions;
CREATE TABLE
network=> \d myt
               Table "pg_temp_11.myt"
     Column     |         Type         | Modifiers
---------------+----------------------+-----------
  id            | integer              |
<snip>
  amount        | numeric              |
  pp_value      | numeric              |
Typed table of type: configurations.tp_transactions

network=> insert into myt select * from configurations.tp_transactions where trans_id=920787;
INSERT 0 1

network=> select configurations.myself(myt.*) from myt;
ERROR:  function configurations.myself(myt) does not exist
LINE 1: select configurations.myself(myt.*) from myt;
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

network=> select configurations.myself(myt.*) from configurations.tp_transactions myt where trans_id=920787;
  myself
--------
      12
(1 row)

In response to

Responses

pgsql-bugs by date

Next:From: Bill MacArthurDate: 2012-04-16 21:42:58
Subject: Re: TYPE of TEMP table does not seem to get set
Previous:From: Tom LaneDate: 2012-04-16 20:11:29
Subject: Re: TYPE of TEMP table does not seem to get set

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