Servoy Forum

Hallo

a friend of mine (Gabriel Wyss) wrote this little scalar funktion for me in Microsoft SQL.
Since I use Postgres I tried to transform to Postgres syntax, but it did'nt work.
Can anybody please translate this little piece of code for me?

-----------------------------------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[OrteListe]
(
@haupt_id SYSNAME
)
RETURNS NVARCHAR(MAX)

AS
BEGIN

DECLARE @s NVARCHAR(MAX);

SELECT @s = COALESCE(@s + N', ', N'') + kind
FROM kind
WHERE haupt_id = @haupt_id
ORDER BY kind

RETURN (@s);
END
-----------------------------------------

Thanks and greetings from Berlin

Ralf

Hi Ralf,

If I read that code correctly the following line aggregates all the values into 1 string delimited with a comma. Am I right?

Code: Select all

SELECT @s = COALESCE(@s + N', ', N'') + kind

Then the PostgreSQL variant of this would be the following (untested) function:

Code: Select all

CREATE FUNCTION public.test(haupt_id character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
   
AS $BODY$
DECLARE result VARCHAR;
BEGIN
   SELECT string_agg(kind ,',') INTO result
   FROM kind
   WHERE haupt_id = $1
   ORDER BY kind;

   RETURN result;
END
$BODY$;

This does require you have plpgsql installed in the schema where the function is.
Of not you can install it using the following command:

Code: Select all

CREATE EXTENSION plpgsql;

If it has to be in schema other than the default 'PUBLIC' then use this:

Code: Select all

CREATE EXTENSION plpgsql SCHEMA schemaName;

For more info on functions in PostgreSQL you can look here.

Hope this helps.

HI Ralf,

I ran a quick test on my SQL code and it turns out that the ORDER BY requires a GROUP BY then. But doing so you don't get the result you want.
So the SQL should be like this:

Code: Select all

WITH orderedvalues AS(
    SELECT kind
    FROM kind
    WHERE haunt_id = $1
    ORDER BY kind
)
SELECT string_agg(kind ,', ')
FROM orderedvalues

So the function would look like this:

Code: Select all

CREATE FUNCTION public.test(haupt_id character varying)
    RETURNS character varying
    LANGUAGE 'plpgsql'
   
AS $BODY$
DECLARE result VARCHAR;
BEGIN
    WITH orderedvalues AS (
        SELECT kind
        FROM kind
        WHERE haunt_id = $1
        ORDER BY kind
    )
    SELECT string_agg(kind ,', ') INTO result
    FROM orderedvalues;

    RETURN result;
END
$BODY$;

Hope this helps.

Hi Robert,

was away some days.
Thanksfor your suggestions.
Yes, the function should aggregate variables delimitetd bei "comma"

I will try and send you my feedback.

Best form Berlin (very hot)
Ralf