[SQL] aggregate function
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
Re: [SQL] aggregate function
=?UTF-8?Q?Viktor_Bojovi=C4=87?= 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 -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Backslash characters in PLPGSQL
We recently upgraded from 8.3 to 8.4 and are noticing a change in
behavior that we can't seem to associate with a particular server
setting.
In 8.3, the following compiles and works perfectly:
CREATE OR REPLACE FUNCTION test_function2(tText TEXT) RETURNS TEXT AS
$BODY$
BEGIN
--
RETURN REPLACE(tText,'\','\\');
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
SELECT test_function2('Bob\');
test_function2
text
"BOB\\"
However, in 8.4, attempting to save the same function results in:
ERROR: unterminated string
CONTEXT: compile of PL/pgSQL function "test_function2" near line 3
It's clear that it's interpreting the backslashes as escaping the
following quote characters, as it compiles and works correctly if I put
a space between the slash and the quote character.
Escaping the backslash with another backslash, with or without the E
character at the start of the string, doesn't resolve anything. In
fact, escaping the backslash like so:
RETURN REPLACE(tText,'\\','');
Works perfectly...to replace two backslashes:
SELECT test_function2('Bob\');
test_function2
text
"BOB\"
SELECT test_function2('Bob\\');
test_function2
text
"BOB"
I've checked the only two server config settings that would appear to
impact this:
standard_conforming_strings (set to ON)
backslash_quote (set to SAFE_ENCODING)
Changing the server setting doesn't appear to have an impact. Does
anybody have a suggestion on what I'm missing?
Garrett Murphy
Re: [SQL] Backslash characters in PLPGSQL
"Garrett Murphy" writes: > We recently upgraded from 8.3 to 8.4 and are noticing a change in > behavior that we can't seem to associate with a particular server > setting. I think you're seeing the effects of this 8.4 patch: http://archives.postgresql.org/pgsql-committers/2009-04/msg00216.php in particular: * In standard_conforming_strings mode, backslash as the last character of a non-E string literal is now correctly taken as an ordinary character; formerly it was misinterpreted as escaping the ending quote. (Since the string also had to pass through the core scanner, this invariably led to syntax errors.) However, as I wrote there, I was under the impression that this fix didn't break any cases that actually worked usefully before 8.4. I tried your example and as far as I can tell, 8.3.x fails on test_function2(), giving "unterminated string" with or without standard_conforming_strings set. But 8.4 accepts it, if you have standard_conforming_strings set. I wonder whether your actual function has yet another improperly terminated string on another line, thereby allowing the 8.3 plpgsql scanner to get back into sync. But it's not obvious why that wouldn't lead to visible misbehavior, or why 8.4 would not like it. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How do you do the opposite of regexp_split_to_table?
I would like to take several rows from a select and have them grouped into one row with the text contents of one field in all the rows concatenated into one field in the single row of the group group. The contents of the other fields are identical across the rows. This is the complementary action to regexp_split_to_table. Grouping by summing numerical fields seems to be straightforward, but grouping by concatenating text fields escapes my search of the documentation. Thanking you for your patience, John -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How do you do the opposite of regexp_split_to_table?
Excerpts from John Gage's message of mar ago 03 16:21:58 -0400 2010: > Grouping by summing numerical fields seems to be straightforward, but > grouping by concatenating text fields escapes my search of the > documentation. You can create a new custom aggregate function that does this. It's pretty straightforward. There are examples in the archives (search for text_concat), but there's also a more modern way involving array_agg and array_to_string. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
