[pgadmin-support] AGGREGATE SYNTAX MISSING IN REVERSE ENGINEERED SQL

[James Prichard]

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));



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])