Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2015
    Posts
    5

    Lightbulb Unanswered: function return a row in a integer array

    Hi,
    I want to write a function in PostGre that returns a row (columns are int type) in a integer array .
    Can anybody help me .. new in PGre Concepts !!

    Scene::
    Tabl :: col1(int) col2(int) col3(int)

    What I try like:
    declare val int[];
    begin
    val := '{}';
    select array(select col1 || ',' || col2 || ',' || col3 from tabl ) into val;
    return val;
    end;

    Cant get the desired result though ..
    Want val{1,1,1} something like this

    Thanks in advance ...

  2. #2
    Join Date
    Jul 2015
    Posts
    7
    you can concatenate array: try this:

    declare val int[];
    begin
    val := '{}';
    select val || col1 || col2 || col3 into val
    from tabl ;
    return val;
    end;

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by vald View Post
    Hi, I want to write a function in PostGre
    The product is either called Postgres or PostgreSQL or simply PG. Never Postgre or PostGre

    Code:
    declare val int[];
    begin
       select array[col1, col2, col3] from tabl 
          into val
       from tabl;
       return val;
    end;
    or even shorter as pure SQL function

    Code:
    create function get_array()
      returns integer[]
    as
    $body$
       select array[col1, col2, col3] 
       from tabl;
    $$
    language sql;

    You have to make sure the query returns exactly one row, otherwise you'll get an error when calling it.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Jul 2015
    Location
    India
    Posts
    1
    DROP FUNCTION IF EXISTS schema.func(parameter);
    CREATE OR REPLACE FUNCTION schema.func(parameter integer)
    RETURNS TABLE(
    col1 integer[],
    col2 integer[])
    LANGUAGE plpgsql
    AS $function$
    DECLARE
    v_sql text := '';
    BEGIN
    v_sql := '
    SELECT array[1,2],array[2,3]';
    RETURN QUERY
    EXECUTE v_sql;

    EXCEPTION
    WHEN OTHERS THEN
    RAISE;
    END;
    $function$;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •