Thread: Function not return zero record

Hello,

I wrote a simple function who checks in a table an address and returns 
the id and coordinates of the corresponding address.

The problem is when I run separately the SQL query, it gives me zero (0) 
entry when I submit a new address - it is what I want as result - and 
when I run my function, it gives me 1 entry with the same address.

Note: I create a pseudo-type to return the result. Is that my problem?

I run Postgresql 7.4 on i686-pc-cygwin (Windows XP), with PostGIS 1.0.



CREATE TABLE adresses (id    int4    PRIMARY KEY DEFAULT nextval('seq_adresse'),
);

SELECT AddGeometryColumn('greffe1','adresses','geom', 4269, 'POINT', 2);


CREATE TYPE getCache AS (id integer,geocode double precision ARRAY[2]);

CREATE FUNCTION getCache(varchar)RETURNS getCache AS '
SELECT id, ARRAY[x(geom),y(geom)] FROM adresses where adresse = $1;
' LANGUAGE sql;



Best regards

Sylvain Racine

Sylvain Racine wrote:
> Hello,
> 
> I wrote a simple function who checks in a table an address and returns 
> the id and coordinates of the corresponding address.
> 
> The problem is when I run separately the SQL query, it gives me zero (0) 
> entry when I submit a new address - it is what I want as result - and 
> when I run my function, it gives me 1 entry with the same address.
> 
> Note: I create a pseudo-type to return the result. Is that my problem?

The problem is the difference between a value of type getCache and a set 
of values of type getCache.

Define your function as returning a SETOF getCache
Call it with SELECT * FROM getCache(...)

HTH

--   Richard Huxton  Archonet Ltd