Thursday, 30 October 2014

CTE inside stored procedure practice example - try to filter messages for interested site(original post)

we have a patient table and a messageq table. messageq.master_id is a foreign key to patient table's entity_id. each patient has a belonged site id.

CREATE TABLE public.test_patient_table (
                entity_id INTEGER NOT NULL,
                site_held_at INTEGER NOT NULL,
                CONSTRAINT entityid_pk PRIMARY KEY (entity_id)
);


CREATE TABLE public.test_messageq_table (
                entity_id VARCHAR NOT NULL,
                master_id INTEGER NOT NULL,
                message_body VARCHAR NOT NULL,
                CONSTRAINT mq_entity_id_pk PRIMARY KEY (entity_id)
);


ALTER TABLE public.test_messageq_table ADD CONSTRAINT test_patient_table_test_messageq_table_fk
FOREIGN KEY (master_id)
REFERENCES public.test_patient_table (entity_id)
ON DELETE NO ACTION
ON UPDATE NO ACTION
NOT DEFERRABLE;

Let's prepare some test data:

--test patient data

insert into test_patient_table values (1, 11111);
insert into test_patient_table values (2, 11111);
insert into test_patient_table values (3, 11111);
insert into test_patient_table values (4, 11111);

insert into test_patient_table values (5, 22222);
insert into test_patient_table values (6, 22222);
insert into test_patient_table values (7, 22222);
insert into test_patient_table values (8, 22222);

insert into test_patient_table values (9, 33333);
insert into test_patient_table values (10, 33333);


insert into test_patient_table values (11, 44444);

--testing message
insert into test_messageq_table values (1, 1, 'aaa');
insert into test_messageq_table values (2, 1, 'aaa');
insert into test_messageq_table values (3, 1, 'aaa');
insert into test_messageq_table values (4, 1, 'aaa');
insert into test_messageq_table values (5, 2, 'aaa');
insert into test_messageq_table values (6, 2, 'aaa');
insert into test_messageq_table values (7, 5, 'aaa');
insert into test_messageq_table values (8, 8, 'aaa');
insert into test_messageq_table values (9, 11, 'aaa');
insert into test_messageq_table values (10, 11, 'bbb');

Problem:
Now we only want to view messages from interested site, for example: 11111 and 44444, how do we do that ?

Solution:
Step 1 :
We need to find out how many patient actually has message in messageq table belongs to the target sites.

Easy:
select distinct test_messageq_table.master_id AS patient_id,
    test_patient_table.site_held_at as site_id
    from test_messageq_table 
    inner join test_patient_table 
    ON test_messageq_table.master_id = test_patient_table.entity_id 
    and site_held_at in (11111,44444) order by patient_id

Step 2:

We need to find out which message belongs to those patient in result of step 1:

select * from test_messageq_table where master_id in 
        (select patient_msg_in_branches.patient_id 
            from patient_msg_in_branches)

Put those together via CTE , we have
WITH patient_msg_in_branches AS (
    select distinct test_messageq_table.master_id AS patient_id,
    test_patient_table.site_held_at as site_id
    from test_messageq_table 
    inner join test_patient_table 
    ON test_messageq_table.master_id = test_patient_table.entity_id 
    and site_held_at in (11111,44444) order by patient_id
),
messages_for_patients AS(
    select * from test_messageq_table where master_id in 
        (select patient_msg_in_branches.patient_id 
            from patient_msg_in_branches)
)select * from messages_for_patients

result:


with a bit more join in the end, we can show the full picture with site id.

WITH patient_msg_in_branches AS (
    select distinct test_messageq_table.master_id AS patient_id,
    test_patient_table.site_held_at as site_id
    from test_messageq_table 
    inner join test_patient_table 
    ON test_messageq_table.master_id = test_patient_table.entity_id 
    and site_held_at in (11111,44444) order by patient_id
),
messages_for_patients AS(
    select * from test_messageq_table where master_id in 
        (select patient_msg_in_branches.patient_id 
            from patient_msg_in_branches)
)
select messages_for_patients.entity_id,
messages_for_patients.master_id ,
messages_for_patients.message_body,
patient_msg_in_branches.site_id from messages_for_patients 
inner join patient_msg_in_branches
ON messages_for_patients.master_id = patient_msg_in_branches.patient_id


Result:


and you can easily wrap this up in a stored procedure, see below.

Stored procedure solutions, one using sql, one using pl/pgsql:

Solution 1 (pl/pgsql):
CREATE OR REPLACE FUNCTION getMessageFromSites(IN ids TEXT) RETURNS 
setof test_messageq_table AS $$ 
DECLARE
       sites INT[];
       result test_messageq_table%rowtype;

BEGIN
       sites = string_to_array(ids,',');
       raise info 'entire array: %', sites;
 return QUERY

    WITH patient_msg_in_branches AS (
        select distinct test_messageq_table.master_id AS patient_id,
        test_patient_table.site_held_at as site_id
        from test_messageq_table 
        inner join test_patient_table 
        ON test_messageq_table.master_id = test_patient_table.entity_id 
        and site_held_at = ANY(sites) order by patient_id
    ),
    messages_for_patients AS(
        select * from test_messageq_table where master_id in 
            (select patient_msg_in_branches.patient_id 
                from patient_msg_in_branches)
    )
    select * from messages_for_patients;

END;     
$$ LANGUAGE plpgsql;
Solution 2 (sql):
CREATE OR REPLACE FUNCTION getMessageFromSites2(ids TEXT) RETURNS 
   setof test_messageq_table 
AS 
$$ 
  WITH patient_msg_in_branches AS (
      select distinct test_messageq_table.master_id AS patient_id,
             test_patient_table.site_held_at as site_id
      from test_messageq_table 
         join test_patient_table ON test_messageq_table.master_id = test_patient_table.entity_id 
                                and site_held_at = ANY (string_to_array($1,',')::int[]) 
  ),
  messages_for_patients AS
  (
    select * 
    from test_messageq_table 
    where master_id in (select patient_msg_in_branches.patient_id 
                        from patient_msg_in_branches)
  )
  select * 
  from messages_for_patients;
$$ 
LANGUAGE sql;
Testing of the code
select * from getMessageFromSites('11111,44444');
select * from getMessageFromSites('22222');
select * from getMessageFromSites('1')
select * from getMessageFromSites('33333')

select * from getMessageFromSites2('11111');
select * from getMessageFromSites2('22222');
select * from getMessageFromSites2('33333');
select * from getMessageFromSites('44444,11111');
select * from getMessageFromSites('1');

Both PG stored procedure are working as expected !

But you may want to return a complex view which doesn't belong to any table, you can do that too !

--solution 1 use sql
CREATE OR REPLACE FUNCTION getMessageFromSitesComplex(ids TEXT) 
    RETURNS table(
       a test_messageq_table.entity_id%type,
       b test_messageq_table.master_id%type ,
       c test_messageq_table.message_body%type,
       d test_patient_table.site_held_at%type)
AS 
$$ 
  WITH patient_msg_in_branches AS (
      select distinct test_messageq_table.master_id AS patient_id,
             test_patient_table.site_held_at as site_id
      from test_messageq_table 
         join test_patient_table ON test_messageq_table.master_id = test_patient_table.entity_id 
                                and site_held_at = ANY (string_to_array($1,',')::int[]) 
  ),
  messages_for_patients AS
  (
    select * 
    from test_messageq_table 
    where master_id in (select patient_msg_in_branches.patient_id 
                        from patient_msg_in_branches)
  )
  select messages_for_patients.entity_id,
messages_for_patients.master_id ,
messages_for_patients.message_body,
patient_msg_in_branches.site_id from messages_for_patients 
inner join patient_msg_in_branches
ON messages_for_patients.master_id = patient_msg_in_branches.patient_id
$$ 
LANGUAGE sql;

--solution 2 use plpgsql
CREATE OR REPLACE FUNCTION getMessageFromSitesComplex2(ids TEXT) 
    RETURNS table(
       a test_messageq_table.entity_id%type,
       b test_messageq_table.master_id%type ,
       c test_messageq_table.message_body%type,
       d test_patient_table.site_held_at%type)
AS
$$
DECLARE
       sites INT[];
       result test_messageq_table%rowtype;
       
BEGIN
       sites = string_to_array(ids,',');
       raise info 'entire array: %', sites;
 return QUERY
 
    WITH patient_msg_in_branches AS (
        select distinct test_messageq_table.master_id AS patient_id,
        test_patient_table.site_held_at as site_id
        from test_messageq_table 
        inner join test_patient_table 
        ON test_messageq_table.master_id = test_patient_table.entity_id 
        and site_held_at = ANY(sites) order by patient_id
    ),
    messages_for_patients AS(
        select * from test_messageq_table where master_id in 
            (select patient_msg_in_branches.patient_id 
                from patient_msg_in_branches)
    )
    select messages_for_patients.entity_id,
messages_for_patients.master_id ,
messages_for_patients.message_body,
patient_msg_in_branches.site_id from messages_for_patients 
inner join patient_msg_in_branches
ON messages_for_patients.master_id = patient_msg_in_branches.patient_id;

END;     
$$ LANGUAGE plpgsql;

--testing data
select * from getMessageFromSitesComplex('11111,22222');
select * from getMessageFromSitesComplex('11111,22222,33333,44444');

select * from getMessageFromSitesComplex2('11111,22222');
select * from getMessageFromSitesComplex2('11111,22222,33333,44444');


Both PG stored procedure are working as expected !


N.B
For named parameters (ids)
1>plpgsql version solution: works fine for BOTH version 9.0 &9.3. 
2>sql version solution: named parameters only works under 9.3 while 9.0 requires  $1 while complaining about ids.

Wednesday, 29 October 2014

Stored procedure - passing array as input parameter

you can pass a string and parse it as a int string if needed.

create table department(id int primary key, name text);

create table employee(id int primary key, name text, salary int, departmentid int references department);

insert into department values (1, 'Management');
insert into department values (2, 'IT');

insert into employee values (1, 'John Smith', 30000, 1);
insert into employee values (2, 'Jane Doe', 50000, 1);
insert into employee values (3, 'Jack Jackson', 60000, 2);


------------------------------
DROP function GetEmployees3();
create or replace function GetEmployees3(ids text) returns 
setof employee as $$
DECLARE
       sites INT[];
BEGIN
       sites = string_to_array($1,',');
       raise info 'entire array: %', sites;

return QUERY
select * from employee where id = ANY(sites);
END;
$$
language 'PLPGSQL';


mselect * from GetEmployees3('1,3');

result as expected, only 1 and 3 returned.


Tuesday, 28 October 2014

postgres stored procedure - return more than one row

Major points

  • Use RETURNS TABLE to define an ad-hoc row type to return.
    Or RETURNS SETOF mytbl to use a pre-defined row type.
  • Use RETURN QUERY to return multiple rows with one command.
for example:
---------------------------------
create table department(id int primary key, name text);

create table employee(id int primary key, name text, salary int, departmentid int references department);

insert into department values (1, 'Management');
insert into department values (2, 'IT');

insert into employee values (1, 'John Smith', 30000, 1);
insert into employee values (2, 'Jane Doe', 50000, 1);
insert into employee values (3, 'Jack Jackson', 60000, 2);


------------------------------
--simple version
create function GetEmployees() returns 
setof employee as 
'select * from employee;' 
language 'sql';

select * from GetEmployees();
--proper version
DROP function GetEmployees2();
create or replace function GetEmployees2() returns 
setof employee as $$
BEGIN
return QUERY
select * from employee;
END;
$$
language 'PLPGSQL';

select * from GetEmployees2();



Monday, 27 October 2014

Postgres - plpgsql return statement

 RETURN

RETURN expression;
RETURN with an expression terminates the function and returns the value of expression to the caller. This form is to be used for PL/pgSQL functions that do not return a set.
When returning a scalar type, any expression can be used. The expression's result will be automatically cast into the function's return type as described for assignments. To return a composite (row) value, you must write a record or row variable as the expression.
If you declared the function with output parameters, write just RETURN with no expression. The current values of the output parameter variables will be returned.
If you declared the function to return void, a RETURN statement can be used to exit the function early; but do not write an expression following RETURN.
The return value of a function cannot be left undefined. If control reaches the end of the top-level block of the function without hitting a RETURN statement, a run-time error will occur. This restriction does not apply to functions with output parameters and functions returning void, however. In those cases a RETURN statement is automatically executed if the top-level block finishes.

38.6.1.2. RETURN NEXT and RETURN QUERY


RETURN NEXT expression;
RETURN QUERY query;
When a PL/pgSQL function is declared to return SETOF sometype, the procedure to follow is slightly different. In that case, the individual items to return are specified by a sequence of RETURN NEXT or RETURN QUERYcommands, and then a final RETURN command with no argument is used to indicate that the function has finished executing. RETURN NEXT can be used with both scalar and composite data types; with a composite result type, an entire "table" of results will be returned. RETURN QUERY appends the results of executing a query to the function's result set. RETURN NEXT and RETURN QUERY can be freely intermixed in a single set-returning function, in which case their results will be concatenated.
RETURN NEXT and RETURN QUERY do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the PL/pgSQLfunction. As successive RETURN NEXT or RETURN QUERY commands are executed, the result set is built up. A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function).
If you declared the function with output parameters, write just RETURN NEXT with no expression. On each execution, the current values of the output parameter variable(s) will be saved for eventual return as a row of the result. Note that you must declare the function as returning SETOF record when there are multiple output parameters, or SETOF sometype when there is just one output parameter of type sometype, in order to create a set-returning function with output parameters.
Here is an example of a function using RETURN NEXT:
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo
    WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE 'plpgsql' ;

SELECT * FROM getallfoo();
Note that functions using RETURN NEXT or RETURN QUERY must be called as a table source in a FROM clause.
Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, as discussed above. That means that if a PL/pgSQLfunction produces a very large result set, performance might be poor: data will be written to disk to avoid memory exhaustion, but the function itself will not return until the entire result set has been generated. A future version of PL/pgSQL might allow users to define set-returning functions that do not have this limitation. Currently, the point at which data begins being written to disk is controlled by the work_mem configuration variable. Administrators who have sufficient memory to store larger result sets in memory should consider increasing this parameter.
Another example:

    DECLARE 
        result    RECORD;
    BEGIN
        FOR result IN (
            SELECT * FROM vos_link_result_set
        ) LOOP
            vos_entity_id := result.vos_entity_id;
            RETURN NEXT; --no return here but append more row to result set
        END LOOP;

        DROP TABLE vos_link_result_set;

        RETURN; -- actual return here
    END;

Troubleshooting:
Q:
I want to do something like this:
CREATE OR REPLACE FUNCTION my_function()
RETURNS TABLE(column1 integer, column2 boolean, ...)
AS $BODY$
DECLARE
    result_row RECORD;
BEGIN
    FOR result_row IN (SELECT * FROM other_function_returning_same_columns()) LOOP
        IF something_wrong_with(result_row) THEN
            RAISE EXCEPTION 'Something went wrong';
        END IF;

        RETURN NEXT result_row;
    END LOOP;
END
$BODY$ LANGUAGE plpgsql STABLE;
This gives me an error:
ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters
A:

RETURN NEXT just returns what the parameters declared in your RETURNS clause (column1, column2, ..) presently hold. You cannot provide a parameter for this form.
There are no OUT parameters.
Parameters declared in RETURNS TABLE(column1 integer, column2 boolean, ...) are effectively the same as OUT parameters.
This should do it:
CREATE OR REPLACE FUNCTION my_function()
  RETURNS TABLE(column1 integer, column2 boolean, ...) AS
$BODY$
BEGIN
   FOR column1, column2, ... IN 
      SELECT * FROM other_function_returning_same_columns()
   LOOP
      IF something_wrong_with(column1, column2, ...) THEN
         RAISE EXCEPTION 'Something went wrong';
      END IF;

      RETURN NEXT;
    END LOOP;
END
$BODY$ LANGUAGE plpgsql STABLE;

Simpler with a registered type

You can further simplify with a registered composite type:
CREATE TYPE mytype (column1 integer, column2 boolean, ...);
Or, if your type happens to match a table definition, you already have that type, because every table name can be used as type name in PostgreSQL. Then simplify:
CREATE OR REPLACE FUNCTION my_function()
  RETURNS SETOF mytype LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   _r mytype;
BEGIN
   FOR _r IN 
     SELECT * FROM other_function_returning_same_columns()
   LOOP
      IF something_wrong_with(_r) THEN
         RAISE EXCEPTION 'Something went wrong';
      END IF;

      RETURN NEXT _r;
   END LOOP;
END
$func$;



Total visitors since Jan 2012

World Visitor Map