Thread: Sql Functions

Sql Functions

From
Randy Neumann
Date:
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;


Re: Sql Functions

From
Tom Lane
Date:
Randy Neumann <Randy_Neumann@centralref.com> writes:
> 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'.

No, the problem is the casts that you've inserted, which are in the
wrong direction.  You've cast char(n) columns to varchar, which produces
results like '456          '::varchar, which does not equal
'456'::varchar.

The reason the query works "by hand" without any casts is that the
untyped constants '456', 'ghi' are assigned type char(n) after seeing
what they are compared to.  Comparison of char(n) values ignores
trailing blanks, so the query gives the answers you expect.  In the
function as you've set it up, the comparisons are done under varchar
rules, and the presence or absence of trailing blanks makes a
difference.

A good general rule for novices is never to use type char(n), period.
You almost certainly want varchar(n), instead.  There are very very
few applications where fixed-width character data is actually what is
wanted.

            regards, tom lane

Newbie trying to load table with data...

From
Kurt Gunderson
Date:
Sorry guys, I am new to Postgresql and have exhausted my reading
material.  I am trying to load a "k_device" table with records from an
pipe-delimited ascii file ("/home/kurt/data/sql.device.d").  In psql, I
get the following...

<FIRST>
test1=> COPY k_device FROM '/home/kurt/data/sql.device.d' USING
DELIMITERS '|';

ERROR:  You must have Postgres superuser privilege to do a COPY directly
to or from a file.  Anyone can COPY to stdout or from stdin.  Psql's
\copy command also works for anyone.
<\FIRST>

Okay so I am not the postgres superuser (nor can I ever be) so I try the
\copy command and I get the following...

<SECOND>
test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" with
delimiters '|'

\copy: parse error at 'delimiters'
<\SECOND>

and...

<THIRD>
test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" using
delimiters '|'

"/home/kurt/data/sql.device.d": No such file or directory
<\THIRD>

What am I doing wrong?  Please help.

K.


Re: Newbie trying to load table with data...

From
Tom Lane
Date:
Kurt Gunderson <kgunders@cbnlottery.com> writes:
> test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" using
> delimiters '|'

> "/home/kurt/data/sql.device.d": No such file or directory

Almost there.  Try it with single quotes or no quotes around the
filename --- double quotes are for SQL identifiers, which the filename
is not.  I tried

test72=# \copy "z" to "barf" using delimiters '|'

which worked, but what I got was

-rw-r--r--   1 tgl        users            0 Aug 13 17:08 "barf"


            regards, tom lane

Re: Newbie trying to load table with data...

From
Oliver Elphick
Date:
On Tue, 2002-08-13 at 19:41, Kurt Gunderson wrote:
> Sorry guys, I am new to Postgresql and have exhausted my reading
> material.  I am trying to load a "k_device" table with records from an
> pipe-delimited ascii file ("/home/kurt/data/sql.device.d").  In psql, I
> get the following...

> <SECOND>
> test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" with
> delimiters '|'
>
> \copy: parse error at 'delimiters'
> <\SECOND>
>
> and...
>
> <THIRD>
> test1=> \copy "k_device" from "/home/kurt/data/sql.device.d" using
> delimiters '|'
>
> "/home/kurt/data/sql.device.d": No such file or directory
> <\THIRD>
>
> What am I doing wrong?  Please help.

You're using the wrong kind of quotes.  The filepath must be in single
quotes:

  \copy k_device from '/home/kurt/data/sql.device.d' using delimiters '|'

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "Watch ye therefore, and pray always, that ye may be
      accounted worthy to escape all these things that shall
      come to pass, and to stand before the Son of man."
                               Luke 21:36