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 |
> 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;
> (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 ,
INSERT INTO work.tp_transactions
WHERE configurations.tp_transactions.trans_id IN (920787);
network=# CREATE OR REPLACE FUNCTION configurations.myself(work.tp_transactions)
network=# create temp table myt () inherits (work.tp_transactions);
network=# select configurations.myself(myt.*) from myt;
In response to
pgsql-bugs by date
|Next:||From: lihao||Date: 2012-04-17 10:10:59|
|Subject: BUG #6595: can't remote access|
|Previous:||From: Bill MacArthur||Date: 2012-04-16 20:29:00|
|Subject: Re: TYPE of TEMP table does not seem to get set|