Postgres - function

Questions and answers regarding general SQL and backend databases

Postgres - function

Postby alk » Sun Jul 22, 2018 1:48 pm

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
alk
 
Posts: 61
Joined: Wed May 21, 2014 8:34 pm
Location: Berlin

Re: Postgres - function

Postby ROCLASI » Sun Jul 22, 2018 8:26 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Postgres - function

Postby ROCLASI » Sun Jul 22, 2018 8:45 pm

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.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: Postgres - function

Postby alk » Mon Jul 30, 2018 11:01 am

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
alk
 
Posts: 61
Joined: Wed May 21, 2014 8:34 pm
Location: Berlin


Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 6 guests