Re: TYPE of TEMP table does not seem to get set
От | Bill MacArthur |
---|---|
Тема | Re: TYPE of TEMP table does not seem to get set |
Дата | |
Msg-id | 4F8C9262.4030204@dhs-club.com обсуждение исходный текст |
Ответ на | Re: TYPE of TEMP table does not seem to get set (Bill MacArthur <webmaster@dhs-club.com>) |
Список | pgsql-bugs |
On 4/16/2012 4:29 PM, Bill MacArthur wrote: > On 4/16/2012 4:11 PM, Tom Lane wrote: >> Bill MacArthur<webmaster@dhs-club.com> writes: >>> I have a view "configurations.tp_transactions" which is a blend of many tables using INNER and LEFT JOINs. I want topopulate a temp table from which I can obtain the data and programmatically write queries to operate on that data usingcertain 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 thetemp table using "OF <new type>, I got the same result. Apparently there is no way to attach a type to a temp table exceptto 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 typeon my function and it will now accept a row of data from my temp table recognizing the type. I am hoping to avoid anyunneeded overhead related to the use of a real table as the parent, including replication, but there does not seem tobe 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)
В списке pgsql-bugs по дате отправления: