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

On 4/16/2012 4:29 PM, Bill MacArthur wrote:
> 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)
>
I also tried creating an explicit TYPE and changing a function to expect that as an argument type. Then after creating the temp table using "OF <new type>, I got the same result. Apparently there is no way to attach a type to a temp table except to make it inherit from a real table.

I have created a skeleton table from which I can create my temp table using INHERITS. Then I changed the argument data type on my function and it will now accept a row of data from my temp table recognizing the type. I am hoping to avoid any unneeded overhead related to the use of a real table as the parent, including replication, but there does not seem to be any way to avoid that possibility.

Still not sure if this is a bug or not... don't know if it really should work the way I was expecting it to.

The final working solution... for now:

network=# create table work.tp_transactions
network-# (id integer ,
<snip>

INSERT INTO work.tp_transactions
SELECT *
FROM configurations.tp_transactions
WHERE configurations.tp_transactions.trans_id IN (920787);

network=# CREATE OR REPLACE FUNCTION configurations.myself(work.tp_transactions)
<snip>

network=# create temp table myt () inherits (work.tp_transactions);
CREATE TABLE

network=# select configurations.myself(myt.*) from myt;
myself
--------
12
(1 row)