pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedaggregate function
2+ messages / 2 participants
[nested] [flat]
* aggregate function
@ 2010-08-03 14:38 Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Viktor Bojović @ 2010-08-03 14:38 UTC (permalink / raw)
To: pgsql-sql
I am trying to make aggregate function of existing function which looks like
this.
CREATE OR REPLACE FUNCTION "grafika"."pov_sphere" (x numeric, y numeric, z
numeric, rad numeric, pigment varchar) RETURNS varchar AS
$body$
DECLARE
_pov varchar;
BEGIN
_pov:='sphere {<'||x||','||y||','||z||'>,'||rad||' '||pigment ||'}';
return _pov;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 100;
Aggregate should concatenate results of pov_sphere using this function
below.
CREATE OR REPLACE FUNCTION "public"."concat" (varchar, varchar) RETURNS
varchar AS
$body$
DECLARE
t varchar;
BEGIN
IF character_length($1) > 0 THEN
t = $1 || $2;
ELSE
t = $2;
END IF;
RETURN t;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
I tried to write this part below, but something is wrong (ERROR: function
grafika.pov_sphere(character varying, numeric, numeric, numeric, numeric,
character varying) does not exist) so I wanted to ask if someone knows how
to solve this problem.
CREATE AGGREGATE "grafika"."agg_pov_sphere" (NUMERIC, NUMERIC, NUMERIC,
NUMERIC, VARCHAR) (
SFUNC = "grafika"."pov_sphere",
STYPE = "varchar",
FINALFUNC = "public"."grp_concat"
);
Thanx in advance.
--
---------------------------------------
Viktor Bojović
---------------------------------------
Wherever I go, Murphy goes with me
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: aggregate function
@ 2010-08-03 14:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Viktor Bojović <viktor.bojovic@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-08-03 14:48 UTC (permalink / raw)
To: Viktor Bojović <viktor.bojovic@gmail.com>; +Cc: pgsql-sql
=?UTF-8?Q?Viktor_Bojovi=C4=87?= <viktor.bojovic@gmail.com> writes:
> I tried to write this part below, but something is wrong (ERROR: function
> grafika.pov_sphere(character varying, numeric, numeric, numeric, numeric,
> character varying) does not exist) so I wanted to ask if someone knows how
> to solve this problem.
Well, it's right: you didn't create such a function. The aggregate
transition function has to take the state datatype as its first
argument. You can't just plug these two randomly-chosen functions
into an aggregate definition and expect the system to intuit what you
want done. I think what you actually need is a transition function
that works something like
if ($1 is null)
return agg_pov_sphere($2,$3,...);
else
return concat($1, agg_pov_sphere($2,$3,...));
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-03 14:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-03 14:38 aggregate function Viktor Bojović <viktor.bojovic@gmail.com>
2010-08-03 14:48 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox