The following code works under 6.5 and doesn't work in 7.0 beta 3. Comments?


------------------snip---------------------------------------
create table paths ( pathnum  serial, pathname text );


CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
           '/opt/brupro/pgsql/lib/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
           HANDLER plpgsql_call_handler
           LANCOMPILER 'PL/pgSQL';
             
/**********************************************************
 * This function makes handling paths MUCH faster on inserts:
 **********************************************************/
CREATE FUNCTION get_path (text) RETURNS integer AS '
  DECLARE
        retval integer;
    BEGIN
        select pathnum into retval from paths where pathname = $1 ;
        if not found then
           insert into paths ( pathname )values ( $1 ) ;
           select pathnum into retval from paths where pathname = $1 ;
        end if ;
        return retval ;
    END;
' LANGUAGE 'plpgsql';

select get_path('/etc');

--------------end snip-------------------------------

-- 
Eric Lee Green                         eric@estinc.com
Software Engineer                      Visit our Web page:
Enhanced Software Technologies, Inc.   http://www.estinc.com/
(602) 470-1115 voice                   (602) 470-1116 fax

Mon, 03 Apr 2000 14:46:20 -0700
Eric Lee Green <pgsql-hackers@estinc.com>
Broken PL/PgSQL for 7.0 beta 3?
> The following code works under 6.5 and doesn't work in 7.0 beta 3. Comments?

Symptoms?

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California

Tue, 04 Apr 2000 01:08:58 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: Broken PL/PgSQL for 7.0 beta 3?
Eric Lee Green <pgsql-hackers@estinc.com> writes:
> The following code works under 6.5 and doesn't work in 7.0 beta 3. Comments?

Please define "doesn't work".  I get (starting with a virgin paths table)

tpc=# select get_path('/etc');
 get_path
----------
        1
(1 row)

tpc=# select get_path('/etc');
 get_path
----------
        1
(1 row)

tpc=# select get_path('/etcz');
 get_path
----------
        2
(1 row)

tpc=# select get_path('/etcz');
 get_path
----------
        2
(1 row)

tpc=# select get_path('/etc');
 get_path
----------
        1
(1 row)

which seems to be the intended behavior.

It might help to know what platform/compiler you are using, too.

			regards, tom lane

Mon, 03 Apr 2000 21:10:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Broken PL/PgSQL for 7.0 beta 3? 