Wither 8.3 doc's on cast operator for domain data types?

From: Steven Lembark <lembark(at)wrkhors(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Wither 8.3 doc's on cast operator for domain data types?
Date: 2009-06-11 15:53:12
Message-ID: 20090611115312.6744bfe4@wrkhors.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Using Postgres 8.3.6, trying to run a query with
bound parameters gives me:

ERROR: XX000: error from Perl function
"expire_facts": operator does not exist: retire
d_date => retired_date at line 56.

"retired_date" is one of a set of domains defined via:

create domain publish_date
timestamp
not null
/* no default */
;

create domain retired_date
timestamp
not null
default 'infinity'
;

create domain insert_date
timestamp
not null
default now()
;

create domain expired_date
timestamp
not null
default 'infinity'
;

At one point I have an insertion trigger that
rejectes duplicate records. It uses a prepared
query via plperl:

spi_prepare
q
{
select
facts_id,
publish,
compare,
value,
large
from
facts
where
identifier_id = $1
and
type_id = $2
and
ontology_id = $3
and
sub_type_id = $4
and
unit_id = $5
and
publish <= $6 /* where the problem lies */
and
retired => $6 /* where the problem lies */
},
(
( 'INTEGER' ) x 5, # $required_fieldz, $default_fieldz
'TIMESTAMP' # $pub
);

I have tried various forms of "cast( $6 as retired_date )",
"$6::retired_date", but skill get the XX000 error.

Google and searching the 8.3 online doc's havn't gotten
me any doc's or examples on syntax for defining the
missing conversion operator.

Q: Where in the 8.3 doc's are the converson operators
(or a cast syntax for bound parameters based on
domains) described?

Or is the problem that I cannot re-cycle $6 when
it has to be cast to two separate domains -- even
if both of them are based on "timestamp"?

Example log entry using "cast( $6 as retired_date ):

LOG: 00000: duration: 0.340 ms bind dbdpg_p24876_1: insert into facts (compare,value,s ub_type_id,dataset_id,identifier_id,unit_id,publish,type_id,ontology_id) values ($1,$2,$3,$4,$5,$6,$7,$8,$9) 2009-06-11 11:28:29.420 EDT lembark 4a31229d.6132 0 3/37 10 DETAIL: parameters: $1 = '0100011010000110110001100010111010100110010011101001011010101 11010110110000001000000101001001100', $2 = '{"bacterium P2"}', $3 = '16', $4 = '3', $5 = '41762', $6 = '0', $7 = '2009-06-10 16:20:18', $8 = '13', $ 9 = '3'
LOCATION: exec_bind_message, postgres.c:1784
INFO: 00000: ARRAY(0xd3db18)
LOCATION: do_spi_elog, SPI.xs:27
STATEMENT: insert into facts (compare,value,sub_type_id,dataset_id,identifier_id,unit _id,publish,type_id,ontology_id) values ($1,$2,$3,$4,$5,$6,$7,$8,$9)
ERROR: XX000: error from Perl function "expire_facts": operator does not exist: retired_date => retired_date at line 56.
LOCATION: plperl_call_perl_trigger_func, plperl.c:1179

This seems odd in particular since

operator does not exist: retired_date => retired_date at line 56.

seems to indicate that retired date cannot be
cast to itself?

This also seems odd since passing in timestamps
with other queries seems to work happily and
perform the convrsion automatically.

thanx

--
Steven Lembark 85-09 90th St.
Workhorse Computing Woodhaven, NY, 11421
lembark(at)wrkhors(dot)com +1 888 359 3508

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-06-11 16:02:45 Re: Wither 8.3 doc's on cast operator for domain data types?
Previous Message Richard Broersma 2009-06-11 14:59:26 Re: When to use cascading deletes?