Key error when converting aggregate to YAML

[greggailly]

Hi, We have created the following aggregate which works well on its own:

--/
CREATE OR REPLACE FUNCTION sum_distinct_on (arg1 numeric, arg2 anyarray)  RETURNS numeric
  VOLATILE
AS $body$
DECLARE 
    sum numeric;
BEGIN
    IF (arg1 IS null) THEN
        if arg2 IS NOT NULL THEN
            sum = coalesce(arg2[2],'0')::numeric;
        ELSE
            sum = 0;
        END IF;
    ELSE
        if arg2 IS NOT NULL THEN
            sum = arg1 + coalesce(arg2[2],'0')::numeric;
        END IF;
    END IF;
    RETURN coalesce(sum,0);
END;
$body$ LANGUAGE plpgsql
/

CREATE AGGREGATE sum_disinct_on (pg_catalog.anyarray)
(
  SFUNC = sum_distinct_on,
  STYPE = numeric
);

However when performing dbtoyaml command we get the following error: KeyError: ('public', 'sum_distinct_on', 'numeric, anyarray') Any idea where it could come from ? Should we change something in the aggregate or is this an issue where Pyrseas ?

Cheers

[jmafc]

I believe this is a variation of issue #175 and a symptom of #185, i.e., the problem is because currently we do not retrieve "built-in" types. This wouldn't be too difficult to do just for dbtoyaml, because we could retrieve all the catalog info and (perhaps) output to YAML any pg_catalog type that was found to be a dependency for one of the user objects. However, for yamltodb the YAML input would have to either bypass any pg_catalog types or implement a workaround (either by querying the target db before processing the YAML or having PG-derived caches, e.g., from src/include/catalog as part of the Pyrseas release).

[jmafc]

Grégoire, if I may ask, are you using both dbtoyaml and yamltodb or just the former? If you're only using dbtoyaml, we could perhaps come up with a solution faster, i.e., by splitting the work needed for #185.

[greggailly]

Yes after a little more research I saw #175 and guessed it was a similar problem. Actually we are currently using a homemade cli made in php inspired by the very early versions of Pyrseas. We are trying to let go of this custom code and switch to pyrseas. So right now schema is dumped to database using our scripts and we are trying to extract them with pyrseas. However the end goal remains to be able to use both dbtoyaml and yamltodb and fully switch to pyrseas. I'll check with our postgres and Python experts in the team if we can find a solution for #185.

[jmafc]

I'd like to offer some suggestions to your experts, but I think it would be best if we continue the conversation under #185, so I'll leave comments there. I'll leave this issue open for a while.