Sql Functions - Mailing list pgsql-novice

Can anyone see what the problem is with this sql function?  When I run the
query outside the function I get the expected result.  It seems that SQL
functions do not like the keyword 'OR'.  Does anyone know if this is a known
bug?

Thanks, Randy

Here are the tables:

create table T1(
    t1c1    integer primary key,
    t1c1d varchar(20)
);
create table T2(
    t2c1    char(20) primary key,
    t2c1d varchar(200)
);
create table T3(
    t3c1     char(20) primary key,
    t3c1d varchar(200)
);
create table T4(
    t4c1     char(20) references T3,
    t4c2 char(20) references T2,
    t4c3   integer references T1
);
create table T5(
    t5c1     char(20) references T3,
    t5c2 char(20) references T3,
    t5c3 integer references T1
);
create table T6(
    t6c1   char(10),
    t6c2 char(20) references T3
);

Sample Data:
T1:
 t1c1      | t1c1d
----------+----------------------
          0 | T1R0
          1 | T1R1
          2 | T1R2

T2:
    t2c1    |  t2c1d
-----------+---------------------------------------------------------------------------------------------------
 123        | stuff
 456        | stuff
 789        | stuff
 0ab        | stuff
 cde        | stuff

T3:
 t3c1  |                               t3c1d
-------+-----------------------------------------------------------------------------
 a1     | stuff
 b2     | stuff
 c3     | stuff

T4:
 t4c1      | t4c2     | t4c3
----------+---------+------------
 b2        | 456      | 0
 a1        | a1        | 3

T5:
 t5c1   | t5c2       | t5c3
-------+-----------+---------------
 c3     | b2          |           2

T6:
   t6c1   |       t6c2
---------+----------------------
 abc      | a1
 def       | b2
 ghi       | c3


SQL query that works from psql:

select T4.t4c3 from T4, T5, T6 where
((T6.t6c2 = T4.t4c1 and T6.t6c1 = 'ghi') or
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1 = 'ghi')) and
(T4.t4c2 = '456')
union
select T5.t5c3 from T4, T5, T6
where
(T4.t4c2 = '456') and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1 = 'ghi')
;
Returns:
t4c3
--------
     1
     2
(2 rows)
If I replace ghi with def
Returns:
t4c3
--------
     1
(1 row)
If I replace ghi with abc
Returns:
t4c3
--------
     3
(1 row)

Here is the function as I originally had it:

create or replace function func1(varchar, varchar) returns setof
integer as
'select T4.t4c3 from T4, T5, T6 where
((T6.t6c2 = T4.t4c1 and T6.t6c1::varchar = $1) or
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)) and
(T4.t4c2::varchar = $2)
union
select T5.t5c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)
;' language sql;

select func1('abc', '456'); returns:
func1
-------
(0 rows)
select func1('def', '456'); returns:
func1
-------
(0 rows)
select func1('ghi', '456'); returns:
func1
-------
   2
(1 row)

Here is the function as I have it now that returns the same values as the sql
query statement above:

create or replace function func1(varchar, varchar) returns setof
integer as
'
select T5.t5c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)
union
select T4.t4c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and
(T5.t5c2 = T4.t4c1 and T6.t6c2 = T5.t5c1 and T6.t6c1::varchar = $1)
union
select T4.t4c3 from T4, T5, T6
where
(T4.t4c2::varchar = $2) and (T6.t6c2 = T4.t4c1 and T6.t6c1::varchar = $1)
union
select T4.t4c3 from T4, T5, T6
where
(T4.t4c2 = ''a1'') and (T6.t6c2 = T4.t4c1 and T6.t6c1::varchar = $1)
;
' language sql;