Function Anomaly?
От | Gary Chambers |
---|---|
Тема | Function Anomaly? |
Дата | |
Msg-id | 302670f20910071925g30322072oc9395e83d592ad2b@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Function Anomaly?
|
Список | pgsql-sql |
All... Given the following type and function: CREATE TYPE getnote_t AS (nid BIGINT, ownerid INTEGER, ownername VARCHAR, hostname VARCHAR, entrytime TIMESTAMP, is_active VARCHAR, is_private VARCHAR, notetext TEXT); CREATE OR REPLACE FUNCTION getnote(INTEGER, BIGINT) RETURNS getnote_t AS $getnote$ SELECT n.nid AS nid, n.ownerid AS ownerid, o.ownername AS ownername, n.hostname AS hostname, n.entrytime::TIMESTAMP(0) AS entrytime, '[' || (CASE WHEN n.is_private = TRUE THEN 'PRIVATE'ELSE 'SHARED' END) || ']' AS is_private, '[' || (CASE WHEN n.is_active = FALSE THEN 'INACTIVE' ELSE 'ACTIVE' END) || ']' AS is_active, n.notetext AS notetext FROM notes n, owners o WHERE (CASE WHEN (n.ownerid != $1 AND n.is_private IS TRUE) THEN FALSE ELSE TRUE END) AND n.ownerid = o.ownerid AND n.nid = $2 $getnote$ LANGUAGE SQL; When I call it with a row where n.is_private is TRUE and n.ownerid IS TRUE, I receive a single row of all null values: notesdb=# select * from getnote(1, 2);nid | ownerid | ownername | hostname | entrytime | is_active | is_private | notetext -----+---------+-----------+----------+-----------+-----------+------------+---------- | | | | | | | (1 row) When I submit the query directly (getnote.sql is simply the query with the CASE statement forced to false): notesdb=# \i getnote.sqlnid | ownerid | ownername | hostname | entrytime | is_private | is_active | notetext -----+---------+-----------+----------+-----------+------------+-----------+---------- (0 rows) Thanks very much in advance for any insight you can provide. -- Gary Chambers /* Nothing fancy and nothing Microsoft! */
В списке pgsql-sql по дате отправления: