From: | Bill MacArthur <webmaster(at)dhs-club(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | TYPE of TEMP table does not seem to get set |
Date: | 2012-04-16 19:46:34 |
Message-ID: | 4F8C771A.2040509@dhs-club.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hello, I am hoping that I'm just missing some kind of syntax tweak, but after experimenting a bit, I cannot seem to get this working.
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 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 recognize the 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-04-16 20:11:29 | Re: TYPE of TEMP table does not seem to get set |
Previous Message | Kevin Grittner | 2012-04-16 18:05:15 | Re: BUG #6592: Return 10061 ($274D) |