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