Thank you everybody for your help - You were indeed correct - Removing the DATE worked, and ran the function from the
client(Although now it fails in the debugger). I'm a little concerned why this didn't work in EMS PostgreSQL Manager
debugger- I'll raise a bug tracker regarding this.
However, I'm now having another problem regarding getting results back from the SELECT INTO query.
OPEN cur_overlap FOR SELECT *, pg_class.RELNAME AS table FROM calendar_entries WHERE (start_date, end_date) OVERLAP
(new_start_date,new_end_date) AND property_id = X AND pg_class.oid = tableoid;
When I run the query directly in psql (Less the OPEN cur_overlap FOR, and substituting in my test data '2005-09-13',
'2005-09-15'),I get one row. However, when the query runs in the function it returns 0 rows.
The only thing I can think of is that when the query runs in the psql I get:
NOTICE: added missing FROM-clause entry for table "pg_class"
I understand why this is happening, but don't know how I would go about re-writing the query to explicitly reference
pg_class- I can't write calendar_entries.table_oid, because that changes the meaning of the query. Is this what causing
theproblem? When I was using the DATE casts in PostgreSQL Manager and stepping through in the debugger, the same query
returned1 row, as expected. I'm very confused and so would appreciate any advice that anyone can offer!
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private
information. If you have received it in error, please notify the sender immediately and delete the original. Any other
useof the email by you is prohibited.