[Home] You are not logged in. Login here

Just my stuff

Oracle concat function

If you need to concatenate varchars:
CREATE OR REPLACE FUNCTION Fnc_Concat_List (cur SYS_REFCURSOR, separator VARCHAR2) RETURN VARCHAR2 IS
    ret VARCHAR2(32000);
    tmp VARCHAR2(4000);
BEGIN
    LOOP
    FETCH cur INTO tmp;
        EXIT WHEN cur%NOTFOUND;
        ret := ret || separator || tmp;
    END LOOP;

    ret := SUBSTR(ret, LENGTH(separator) + 1);
    RETURN ret;
END;
/

Example:
CREATE TABLE hallo (ID number(10) PRIMARY KEY, NAME varchar2(255));
INSERT INTO hallo (ID, NAME) VALUES (1, 'a');
INSERT INTO hallo (ID, NAME) VALUES (2, 'b');
INSERT INTO hallo (ID, NAME) VALUES (3, 'c');
COMMIT;

SELECT fnc_concat_list(CURSOR(SELECT name from hallo), ', ') from dual;

Oracle pipelined function

  
CREATE TABLE hallo (ID number(10) PRIMARY KEY, NAME varchar2(255));
INSERT INTO hallo (ID, NAME) VALUES (1, 'a');
INSERT INTO hallo (ID, NAME) VALUES (2, 'b');
INSERT INTO hallo (ID, NAME) VALUES (3, 'c');
COMMIT;

CREATE OR REPLACE PACKAGE hoi IS
    TYPE hallo_lines IS TABLE OF hallo%ROWTYPE;
    FUNCTION h RETURN hallo_lines pipelined;
END;

CREATE OR REPLACE PACKAGE BODY hoi IS

    FUNCTION h RETURN hallo_lines pipelined IS
        r_hallo         hallo%ROWTYPE;
    BEGIN

        FOR i IN (SELECT ID, NAME FROM hallo)
        LOOP
            r_hallo := i;
            -- r_hallo.ID := i.ID;
            -- r_hallo.NAME := i.NAME;

            PIPE ROW(r_hallo);
        END LOOP;
    END;
END;

select * from table(hoi.h);

group_concat on postgresql

CREATE TABLE produk
(
seq_id serial NOT NULL,
name character varying(100) NOT NULL
) ;

INSERT INTO produk (seq_id, name) VALUES (1, 'beer');
INSERT INTO produk (seq_id, name) VALUES (2, 'in');
INSERT INTO produk (seq_id, name) VALUES (3, 'beer');
INSERT INTO produk (seq_id, name) VALUES (4, 'can');
INSERT INTO produk (seq_id, name) VALUES (6, 'goods');
INSERT INTO produk (seq_id, name) VALUES (7, 'goods');
INSERT INTO produk (seq_id, name) VALUES (8, 'can');
INSERT INTO produk (seq_id, name) VALUES (5, 'goods');

MySQL:

select name, group_concat(cast(seq_id as char)) as id_of_duplicates
from produk
group by name
order by name;

PostgreSQL:

create aggregate array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

CREATE OR REPLACE FUNCTION _group_concat(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);

first approach:

select name, array_accum(seq_id)
from produk
group by name

select name, array_to_string(array_accum(seq_id), ',')
from produk
group by name;

second approach (mysql-compatible approach):

select name, group_concat(seq_id) as id_of_duplicates
from produk
group by name
order by name;

MySQL GROUP_CONCAT with ordering:

select name, group_concat(cast(seq_id as char) order by seq_id) as id_of_duplicates
from produk
group by name
order by name;

PostgreSQL equivalent:

select name, group_concat(distinct seq_id) as id_of_duplicates
from produk
group by name
order by name;

using customized sort:

select name, group_concat(seq_id) as id_of_duplicates
from
(
select name, seq_id
from produk
order by name, seq_id
) as x
group by name

I found this here.

Install postgresql on Ubuntu

Here is the FM.
It comes down to

sudo -u postgres createdb mydb
sudo -u postgres createuser newuser
sudo -u postgres psql postgres 

ALTER USER postgres WITH ENCRYPTED PASSWORD ' <***password***> ';
ALTER USER newuser WITH ENCRYPTED PASSWORD ' <***password***> ';
\q