Thread: TYPE of TEMP table does not seem to get set
Hello, I am hoping that I'm just missing some kind of syntax tweak, but after experimenting a bit, I cannot seem to get thisworking. I have a view "configurations.tp_transactions" which is a blend of many tables using INNER and LEFT JOINs. I want to populatea temp table from which I can obtain the data and programmatically write queries to operate on that data using certainPLPGSQL functions in a uniform fashion. I have several functions which all expect a single argument of the type "configurations.tp_transactions". I am running 9.0.5 on CentOS 5 64 bit Here is the schema of "configurations.tp_transactions" network=> \d configurations.tp_transactions View "configurations.tp_transactions" Column | Type | Modifiers ---------------+----------------------+----------- id | integer | spid | integer | sponsor_mtype | character varying(5) | sponsor_spid | integer | membertype | character varying | trans_type | smallint | trans_id | integer | trans_date | date | discount_amt | numeric(6,2) | cv_mult | real | reb_mult | real | comm_mult | real | rebate | numeric | reb_com | numeric(5,4) | pp | numeric(6,5) | cap | integer | receivable | numeric(8,2) | ma_id | integer | ma_spid | integer | ma_mtype | character varying(5) | amount | numeric | pp_value | numeric | network=> SELECT * INTO TEMP TABLE myt FROM configurations.tp_transactions WHERE membertype <> 'x' AND configurations.tp_transactions.trans_id IN (920787); SELECT 1 network=> \d myt Table "pg_temp_47.myt" Column | Type | Modifiers ---------------+----------------------+----------- id | integer | spid | integer | sponsor_mtype | character varying(5) | sponsor_spid | integer | membertype | character varying | trans_type | smallint | trans_id | integer | trans_date | date | discount_amt | numeric(6,2) | cv_mult | real | reb_mult | real | comm_mult | real | rebate | numeric | reb_com | numeric(5,4) | pp | numeric(6,5) | cap | integer | receivable | numeric(8,2) | ma_id | integer | ma_spid | integer | ma_mtype | character varying(5) | amount | numeric | pp_value | numeric | network=> \df configurations.myself List of functions Schema | Name | Result data type | Argument data types | Type ----------------+--------+------------------+--------------------------------+-------- configurations | myself | integer | configurations.tp_transactions | normal (1 row) 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 the temp table first using this syntax and then inserting into it, but function still did not recognizethe type. network=> CREATE TEMP TABLE myt OF configurations.tp_transactions; CREATE TABLE network=> \d myt Table "pg_temp_11.myt" Column | Type | Modifiers ---------------+----------------------+----------- id | integer | spid | integer | sponsor_mtype | character varying(5) | sponsor_spid | integer | membertype | character varying | trans_type | smallint | trans_id | integer | trans_date | date | discount_amt | numeric(6,2) | cv_mult | real | reb_mult | real | comm_mult | real | rebate | numeric | reb_com | numeric(5,4) | pp | numeric(6,5) | cap | integer | receivable | numeric(8,2) | ma_id | integer | ma_spid | integer | ma_mtype | character varying(5) | 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)
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 to populatea temp table from which I can obtain the data and programmatically write queries to operate on that data using certainPLPGSQL 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
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 to populatea temp table from which I can obtain the data and programmatically write queries to operate on that data using certainPLPGSQL 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)
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)