Bglet by Jim Service: Multiple copies of rows in PostgreSQL

July 15, 2006

Multiple copies of rows in PostgreSQL

I had a rather complicated view where I needed to duplicate each row and change one column in every other row. I came up with this function:

-- Function: numbers(count int4)

-- DROP FUNCTION numbers(count int4);

CREATE OR REPLACE FUNCTION numbers(count int4)
  RETURNS SETOF int4 AS
$BODY$
BEGIN
  -- Usage: select * from numbers(10)
  -- returns a column of integers from 0 to 9
  --
  for i in 0 .. count - 1 loop
    return next i ;
  end loop ;
  return ;
END ;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE;

To make 3 copies of each row of table I could use the following:

select * from mytable,numbers(3)

Posted by jservice at July 15, 2006 10:31 PM

Comments