Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: Creating Muliple Rows From a Single Row

    I've been wondering whether there is a good way to use SQL queries and PL/pgSQL functions to create multiple rows from a single row of data. To be specific, consider a dataset set up as follows:

    ID1 C33
    ID2 369


    I want to create a dataset that looks like this:

    ID1 C
    ID1 3
    ID1 3
    ID2 3
    ID2 6
    ID2 9

    i.e., one row of the old table is used to create several rows in the new table.

    Has anyone done anything like this before?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I think this can only be done with a custom function:

    Code:
    create or replace function chars_to_rows()
      returns setof strange_table
      language plpgsql
    as
    $body$
    declare
       entry record;
       result_row strange_table%rowtype;
       len integer;
    begin
      for entry in select * from strange_table loop
        for i in 1 .. char_length(entry.some_value) loop
          result_row.id = entry.id;
          result_row.some_value = substring(entry.some_value, i, 1);
          return next result_row;
        end loop;
      end loop;
      return;
    end;
    $body$
    This is using the following table definition:
    Code:
    create table strange_table
    (
      id varchar(10),
      some_value varchar(10)
    );
    It can be used like this:
    Code:
    select * from chars_to_rows()
    Note that the function will not return a row if the length of some_value is zero.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Just realized there is a much easier solution

    Code:
    select id, regexp_split_to_table(some_value, '')
    from strange_table;

  4. #4
    Join Date
    Feb 2011
    Posts
    3
    shammat,

    Thanks a lot for your suggestion. Even though the second is easier for this example, I'm going to study your first suggestion closely. I trying to create expanded datasets like this combining several columns of data.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by RDittmar View Post
    shammat,

    Thanks a lot for your suggestion. Even though the second is easier for this example, I'm going to study your first suggestion closely. I trying to create expanded datasets like this combining several columns of data.
    The first solution is definitely more flexible. The only drawback is that table functions build up the whole result set on the server before returning it to the client (rather than streaming it "on-the-fly" as a regular query does).

    So if you are going to return really large result sets this might be a problem (depending on the capabilities of your server)

Posting Permissions

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