unpacking composite values

From: Joshua N Pritikin <jpritikin(at)pobox(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: unpacking composite values
Date: 2005-06-24 04:56:49
Message-ID: 20050624045649.GD7609@always.joy.eth.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi, I'm using DBD::Pg 1.32 and postgresql 8.0.3. At first I posted to the
DBD::Pg mailing list because I though the following might be a perl-specific
issue. Now I'm not so sure.

I have a query that looks like this:

select
*,
query_gt_sentence_param(s.expert_id, s.construal_id)
from rating r
join c_stat s on (r.construal_expert_id = s.expert_id and
r.construal_id = s.construal_id)

And query_gt_sentence_param returns a composite value. In perl, I get:

$VAR1 = {
'c_stat_mean' => '-1',
'rating_value' => '-1',
'query_gt_sentence_param' => '(Brian,"admiring Brian","The
Director",m,a)',
'z' => '0'
};

Note the query_gt_sentence_param is returned as a string instead of as
what I what, separate columns.

Is there an easy way to get it to break out each column as a separate key
value pair? Maybe I can flatten the composite value into regular fields?

The other thing I tried is:

select
*,
(query_gt_sentence_param(s.expert_id, s.construal_id)).mindreader,
(query_gt_sentence_param(s.expert_id, s.construal_id)).topic,
(query_gt_sentence_param(s.expert_id, s.construal_id)).agent,
(query_gt_sentence_param(s.expert_id, s.construal_id)).sex
from rating r
join c_stat s on (r.construal_expert_id = s.expert_id and
r.construal_id = s.construal_id)

This works, but explain analyze shows that it is calling my function 4 times
instead of once. The function is declared as STABLE but I guess the
optimizer can't prove that the function returns the same results within the
same query.

Any suggestions?

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Johan De Koning 2005-06-24 11:03:39 Combining images
Previous Message Jeremy Yager 2005-06-23 21:17:25 Connecting to Postgres via ADO/Data Environment in VB6