unpacking composite values

Поиск
Список
Период
Сортировка
От Joshua N Pritikin
Тема unpacking composite values
Дата
Msg-id 20050624045649.GD7609@always.joy.eth.net
обсуждение исходный текст
Ответы Re: unpacking composite values  (Joshua N Pritikin <jpritikin@pobox.com>)
Список 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?

Вложения

В списке pgsql-novice по дате отправления:

Предыдущее
От: "Jeremy Yager"
Дата:
Сообщение: Connecting to Postgres via ADO/Data Environment in VB6
Следующее
От: Johan De Koning
Дата:
Сообщение: Combining images