[UPHPU] Postgres Function

[Ray Hunter]

Dave Long wrote:
> I have the following Postgres resultset that returns an empty
> resultset when trying to call it as "SELECT * FROM maxServices('rat');
> 
> Here is the function:
> 
> CREATE OR REPLACE FUNCTION maxServices(char) RETURNS SETOF holder AS $$
> DECLARE
>     r holder%rowtype;
> BEGIN
>     EXECUTE 'CREATE TEMPORARY TABLE maxservices (hostname
> varchar(45), status int)';
>     EXECUTE 'INSERT INTO maxservices (hostname,status) SELECT
> hostname, max(status) FROM services GROUP BY hostname';
>     FOR r IN
>      SELECT memberships.hostname, h.ip_address, h.description,
> h.status, h.plugin_output, maxservices.status AS serviceStatus
>            FROM memberships, hosts AS h, maxservices
>            WHERE memberships.hostname=h.hostname AND
> maxservices.hostname=h.hostname AND memberships.customer = '$1'
>            ORDER BY h.description
>       LOOP
>      RETURN NEXT r;
>     END LOOP;
>     EXECUTE 'DROP TABLE maxservices';
>     RETURN;
> END
> $$
> LANGUAGE plpgsql;
> 
> and here is the data type holder:
> 
> CREATE TYPE holder AS (hostname varchar, ip_address varchar,
> description varchar, status smallint, plugin_output varchar,
> serviceStatus smallint);
> 
> What am I doing wrong to make this return an empty result set.  I
> verified that the select statement in the function worked as expected.
> And yes, there is data in the tables.

are you getting any error messages when you run it from the command line (psql)?

--
ray