On 4/16/2012 4:29 PM, Bill MacArthur wrote:
On 4/16/2012 4:11 PM, Tom Lane wrote:
Bill MacArthur<webmas...@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 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)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
