CODEWITHSUNDEEP
postgresqlstored-procedurespattern-matchingplpgsql
Amit Sharad
Amit Sharad

Reputation: 1201

Return a composite type in PostgreSQL

I extract start value and end value from an input string and store them it in local variables which I need to return as

CREATE OR REPLACE FUNCTION test.getweeks_data(paramweek character varying)
  RETURNS SETOF test.weekly_data_type AS
$BODY$
DECLARE
     v_start_date date;
     v_end_date date;
     r test.weekly_data_type%rowtype;

BEGIN

    v_start_date := to_date(substring( paramweek, '^...........'), 'DD Mon YYYY');
    v_end_date := to_date(substring( paramweek, '...........$'),'DD Mon YYYY'); 

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

My type is defined as 

CREATE TYPE test.weekly_data_type AS
   (week_start_date date,
    week_end_date date
    );

Please suggest a suitable way to return v_start_date and v_end_date.

Upvotes: 0

Views: 581

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659217

Your function can (and should) be improved in multiple places:

CREATE OR REPLACE FUNCTION getweeks_data(paramweek text
                                       , OUT week_start_date date
                                       , OUT week_end_date date) AS
$func$
SELECT to_date(left (paramweek, 11), 'DD Mon YYYY')
     , to_date(right(paramweek, 11), 'DD Mon YYYY')
$func$  LANGUAGE sql IMMUTABLE

  • Do not use SETOF, you only return a single row. You could use your custom composite type, but I suggest to simplify with OUT parameters as demonstrated.

  • Make the function IMMUTABLE, not VOLATILE. Better performance for repeated calls.

  • You don't need plpgsql at all. A simple SQL function does the job. Can be "inlined" in big queries, better performance for repeated calls.

  • Use left() and right() instead of regular expression. Simpler, shorter, faster.

Upvotes: 2

Dwayne Towell
Dwayne Towell

Reputation: 8623

r.week_start_date := v_start_date;
r.week_end_data := v_end_date;
RETURN NEXT r;

Upvotes: 1

Related Questions