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?
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 |