I'm getting the above error when i try to replace a function of mine. It
seems i have two problems: the latest dump (through phpPGAdmin) works
fine, except that a function that should return a record was replaced
without the column definition list, so calls on it are failing.
from pg_dump:
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype() RETURNS
SETOF record AS $$
should be:
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text,
OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$
So, i tried re-defining the function with the OUT params, and was hit
with the error in the subject line. I was able to DROP it first, then
re-create it. Now everything seems fine. But does anyone know what the
error means?
And why does the function definition in the db dump not reflect that OUT
params are called for?
Here's the entire function, fwiw:
-- snip --
CREATE OR REPLACE FUNCTION getserviceprovidertotalsbytype(OUT name text,
OUT id INT4, OUT total INT4) RETURNS SETOF record AS $$
DECLARE
rec record;
BEGIN
FOR rec IN
EXECUTE 'SELECT id, name, 1 AS total FROM service_type ORDER BY NAME ASC'
LOOP
name := rec.name;
id := rec.id;
SELECT INTO rec.total SUM(CASE sp.accepted WHEN TRUE THEN 1 ELSE 0 END)
FROM service_provider AS sp WHERE sp.id IN
(
SELECT spst.service_provider_id FROM service_provider_service_type AS
spst WHERE spst.service_type_id = rec.id
);
-- If none for this service type, give it a total of zero
IF rec.total IS NULL THEN
SELECT INTO total 0;
ELSE
total := rec.total;
END IF;
RETURN NEXT;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql IMMUTABLE;
-- snip --
brian
