Re: String aggregate function

Hello,

I am trying to create a database trigger which inserts into a second table. I have created the following function in accordance with the reference manual documentation (7.2).

CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER AS '

INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID, ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;

SELECT 1 ;

' LANGUAGE SQL ;

It fails with the cryptic error "parse error at or near ;". Can anyone tell me what is wrong with this syntax?

Regards

You need to put your aliases in:
value1 alias for $1;
etc.

Hello,
>
> I am trying to create a database trigger which inserts into a second
> table. I have created the following function in accordance with the
> reference manual documentation (7.2).
>
>
> CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER
> AS '
>
> INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
> ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
>
> SELECT 1 ;
>
> ' LANGUAGE SQL ;
>
>
>
> It fails with the cryptic error "parse error at or near ;". Can anyone
> tell me what is wrong with this syntax?
>
> Regards

Hi Susan,

I had a similar problem with a function using pl/pgsql. I checked the docs
(7.3) for it and discovered the following info:

"A trigger procedure is created with the CREATE FUNCTION command as a function
with no arguments and a return type of OPAQUE"

http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=plpgsql-trigger.html

Regards,

-Andreas

> Hello,
>
> I am trying to create a database trigger which inserts into a second table.
> I have created the following function in accordance with the reference
> manual documentation (7.2).
>
>
> CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER AS
> '
>
> INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
> ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
>
> SELECT 1 ;
>
> ' LANGUAGE SQL ;
>
>
>
> It fails with the cryptic error "parse error at or near ;". Can anyone
> tell me what is wrong with this syntax?
>
> Regards

--
Andreas Schmitz - Phone +49 201 8501 318
Cityweb-Technik-Service-Gesellschaft mbH
Friedrichstr. 12 - Fax +49 201 8501 104
45128 Essen - email a(dot)schmitz(at)cityweb(dot)de

Hi Andrea,

That was my first attempt (i.e. using opaque) but unfortunately when I use
"opaque" it tells me that SQL functions cannot return opaque. The parse
error is also occurring on the create function statement prior to any
knowledge that the function is being used for a trigger.

Regards

----- Original Message -----
From: "Andreas Schmitz" <a(dot)schmitz(at)cityweb(dot)de>
To: "Susan Hoddinott" <susan(at)hexworx(dot)com>; <pgsql-sql(at)postgresql(dot)org>
Sent: Friday, March 14, 2003 10:52 PM
Subject: Re: [SQL] Create function statement with insert statement

>
> Hi Susan,
>
> I had a similar problem with a function using pl/pgsql. I checked the docs
> (7.3) for it and discovered the following info:
>
> "A trigger procedure is created with the CREATE FUNCTION command as a
function
> with no arguments and a return type of OPAQUE"
>
>
http://www.postgresql.org/docs/view.php?version=7.2&idoc=1&file=plpgsql-trig
ger.html
>
>
> Regards,
>
> -Andreas
>
>
>
>
> > Hello,
> >
> > I am trying to create a database trigger which inserts into a second
table.
> > I have created the following function in accordance with the reference
> > manual documentation (7.2).
> >
> >
> > CREATE OR REPLACE FUNCTION orderinsert(INTEGER, VARCHAR) RETURNS INTEGER
AS
> > '
> >
> > INSERT INTO HEXORDERS ( CUSTOMER_ID, ORDER_KEY, DISTRIBUTOR_ID,
> > ORDER_AMOUNT, ORDER_GST ) VALUES ( $1, $2, 1, 0, 0 ) ;
> >
> > SELECT 1 ;
> >
> > ' LANGUAGE SQL ;
> >
> >
> >
> > It fails with the cryptic error "parse error at or near ;". Can anyone
> > tell me what is wrong with this syntax?
> >
> > Regards
>
> --
> Andreas Schmitz - Phone +49 201 8501 318
> Cityweb-Technik-Service-Gesellschaft mbH
> Friedrichstr. 12 - Fax +49 201 8501 104
> 45128 Essen - email a(dot)schmitz(at)cityweb(dot)de

Susan Hoddinott wrote:
> Hi Andrea,
>
> That was my first attempt (i.e. using opaque) but unfortunately when I use
> "opaque" it tells me that SQL functions cannot return opaque. The parse
> error is also occurring on the create function statement prior to any
> knowledge that the function is being used for a trigger.
>

I haven't followed this thread too closely, but if you are trying to use
a SQL function for a trigger, it won't work. From the docs:
(http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/triggers.html)

"Trigger functions can be written in C and most procedural languages,
but not in SQL"

Try re-writing your function in PL/pgSQL. BTW, I think I saw from your
other post that you don't have PL/pgSQL installed in the database you
are using. See the createlang program or CREATE LANGUAGE statement:
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/app-createlang.html
http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-createlanguage.html

HTH,

Joe

Hi all,

I want to make an aggregate function that concatenates strings from a
certain column into one cell in a group by clause.
For example I have the following table :

Type Text
=================
1 text1
2 text2
1 text3
3 text4
2 text5

I need a query that group by type and concats the text columns to
produce something like the following :

1 text1, text3
2 text2, text5
3 text4

I am aware that this can be done through recursive joins but this is too
complicated and heavy.

Any suggestions?

Regards

Objectz wrote:
> Hi all,
>
> I want to make an aggregate function that concatenates strings from a
> certain column into one cell in a group by clause.
> For example I have the following table :
>
> Type Text
> =================
> 1 text1
> 2 text2
> 1 text3
> 3 text4
> 2 text5
>
> I need a query that group by type and concats the text columns to
> produce something like the following :
>
> 1 text1, text3
> 2 text2, text5
> 3 text4
>
> I am aware that this can be done through recursive joins but this is too
> complicated and heavy.
>
> Any suggestions?
>
> Regards
Look at this site (Aggregate Functions):
http://www.brasileiro.net:8080/postgres/cookbook/

Regards,
Tomasz Myrta

Tomsaz,

Thnx a lot for the great reference It has what I need.

create function comma_aggregate(text,text) returns text as '
begin
if (length($1) > 0 ) then
return $1 || ', ' || $2;
else
return $2;
end if;
end;
' language 'plpgsql';

-- create the aggregate function

create aggregate comma (basetype=text, sfunc=comma_aggregate,
stype=text, initcond='' );

I need some help to expand on its funcitonality instead of calling
comma(col_name) to produce comma-seperated values I need to call
something like concattext(prefix, col_name, suffix) wich returns the
list in one column with the prefix and suffix aroung each entry as
specified.

This will enable me to produce something like this result

> 1 (text1) (text3)
> 2 (text2) (text5)
> 3 (text4)

With this query
Select type, concattext('(', text, ') ') from table group by type

Any help on how to convert the above comma function to receive the
prefix and suffix parameters

Regards

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Tomasz Myrta
Sent: Monday, March 17, 2003 11:16 AM
To: Objectz
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] String aggregate function

Objectz wrote:
> Hi all,
>
> I want to make an aggregate function that concatenates strings from a
> certain column into one cell in a group by clause. For example I have
> the following table :
>
> Type Text
> =================
> 1 text1
> 2 text2
> 1 text3
> 3 text4
> 2 text5
>
> I need a query that group by type and concats the text columns to
> produce something like the following :
>
> 1 text1, text3
> 2 text2, text5
> 3 text4
>
> I am aware that this can be done through recursive joins but this is
> too complicated and heavy.
>
> Any suggestions?
>
> Regards
Look at this site (Aggregate Functions):
http://www.brasileiro.net:8080/postgres/cookbook/

Regards,
Tomasz Myrta

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Uz.ytkownik Objectz napisa?:
> Tomsaz,
>
> Thnx a lot for the great reference It has what I need.
>
> create function comma_aggregate(text,text) returns text as '
> begin
> if (length($1) > 0 ) then
> return $1 || ', ' || $2;
> else
> return $2;
> end if;
> end;
> ' language 'plpgsql';
>
> -- create the aggregate function
>
> create aggregate comma (basetype=text, sfunc=comma_aggregate,
> stype=text, initcond='' );
>
>
> I need some help to expand on its funcitonality instead of calling
> comma(col_name) to produce comma-seperated values I need to call
> something like concattext(prefix, col_name, suffix) wich returns the
> list in one column with the prefix and suffix aroung each entry as
> specified.
>
> This will enable me to produce something like this result
>
>
>>1 (text1) (text3)
>>2 (text2) (text5)
>>3 (text4)
>
>
> With this query
> Select type, concattext('(', text, ') ') from table group by type
>
> Any help on how to convert the above comma function to receive the
> prefix and suffix parameters
>
> Regards

Change this function to separate fields only with space character
(instead of comma) and use as below:

select comma('(' || text ')') ...

Regards,
Tomasz Myrta

Uz.ytkownik Tomasz Myrta napisa?:

> Change this function to separate fields only with space character
> (instead of comma) and use as below:
>
> select comma('(' || text ')') ...
Sorry:
select comma('(' || text || ')') ...

Tomasz

Didn't know this was possible.
Thnx a lot for ur support

-----Original Message-----
From: Tomasz Myrta [mailto:jasiek(at)klaster(dot)net]
Sent: Monday, March 17, 2003 12:59 PM
To: Objectz
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] String aggregate function

Uz.ytkownik Tomasz Myrta napisa?:

> Change this function to separate fields only with space character
> (instead of comma) and use as below:
>
> select comma('(' || text ')') ...
Sorry:
select comma('(' || text || ')') ...

Tomasz