From: | "James Prichard" <james(dot)prichard(at)markit(dot)com> |
---|---|
To: | <pgadmin-support(at)postgresql(dot)org> |
Subject: | AGGREGATE SYNTAX MISSING IN REVERSE ENGINEERED SQL |
Date: | 2005-03-04 11:42:07 |
Message-ID: | A8D8891C40113445A567195896DC83AE8DFDED@exch_svr.Dadd.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
pgAdmin3 Version 1.3.0 (Mar 3 2005)
running on Win 2000 Pro (IA32)
MINOR GLITCH REPORT
- the "aggfunction(argtype)" is necessary for changing owner whereas
the reverse engineered sql display shows just the "aggfunction"
- a prior version of pgAdmin3 had a similar error in the sql to drop
the function, this is fixed in 1.3.0, except not terminated by a
semicolon - note, that version is not yet bundled with the Win32
installer for Postgres
- "ffunc" is shown instead of "finalfunc" (maybe confused by the help
page's example)
== from pgAdmin screen ====================================
-- Aggregate: my_aggfunc
-- DROP AGGREGATE my_aggfunc(int) <<<< missing ';'
CREATE AGGREGATE my_aggfunc(
BASETYPE=int,
SFUNC=my_aggfunc_item,
STYPE=int[],
FFUNC=my_aggfunc_final,
INITCOND='{0,0,99,99,0,0,0}'
);
ALTER AGGREGATE my_aggfunc OWNER TO my_username; <<<< missing '(int)'
Here is what the interface shows me
-- Aggregate: my_aggfunc
-- DROP AGGREGATE my_aggfunc(int)
CREATE AGGREGATE my_aggfunc(
BASETYPE=int,
SFUNC=my_aggfunc_item,
STYPE=int[],
FFUNC=my_aggfunc_final,
INITCOND='{0,0,99,99,0,0,0}'
);
ALTER AGGREGATE my_aggfunc OWNER TO my_username;
== corrected version ====================================
-- Aggregate: my_aggfunc
-- DROP AGGREGATE my_aggfunc(int);
CREATE AGGREGATE my_aggfunc(
BASETYPE=int,
SFUNC=my_aggfunc_item,
STYPE=int[],
FINALFUNC=my_aggfunc_final,
INITCOND='{0,0,99,99,0,0,0}'
);
ALTER AGGREGATE my_aggfunc(int) OWNER TO my_username;
===================
to help test the syntax here are trivial working functions
/*
DROP AGGREGATE my_aggfunc(int);
DROP FUNCTION my_aggfunc_item(int[],int);
DROP FUNCTION my_aggfunc_final(f int[]);
*/
CREATE FUNCTION my_aggfunc_item(int[],int)
RETURNS int[] AS 'BEGIN IF $2<$1[1] THEN return array[$2]||$1[2];
ELSE IF $2<=$1[2] THEN return $1;
ELSE return $1[1:1]||$2;END IF;END IF;END'
LANGUAGE 'plpgsql' IMMUTABLE;
CREATE FUNCTION my_aggfunc_final(x int[]) RETURNS varchar(3) AS 'BEGIN
return array_to_string(x,''-'');END' LANGUAGE 'plpgsql' IMMUTABLE;
CREATE AGGREGATE
my_aggfunc(BASETYPE=int,SFUNC=my_aggfunc_item,STYPE=int[],FINALFUNC=my_a
ggfunc_final,INITCOND='{100,100}');
SELECT my_aggfunc(cast(200*random() as int));
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Held | 2005-03-04 16:59:28 | EditGrid |
Previous Message | Dave Page | 2005-03-03 08:53:33 | Re: pgAdminIII and the LogFile - useless for debugging |