Need help building SQL query - Mailing list pgsql-novice

Hello,

I have a problem as to which I do not know
how to solve. My tables are built like this:

Timereportbreak consists of four breaks for
each timereport. They are ordered by the
indexnumber (0-3). They are connected to the
timereport table using the column timereportid.

[code]
CREATE TABLE timereportbreak
(
  timereportid int4 NOT NULL DEFAULT 0,
  starttime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  endtime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  indexnumber int4 NOT NULL DEFAULT 0
)
WITH OIDS;
ALTER TABLE timereportbreak OWNER TO postgres;
[/code]

Timereport consists of the following columns..

[code]
CREATE TABLE timereport
(
  employeesignature varchar(6) NOT NULL DEFAULT ''::character varying,
  startdate date,
  workedtimetotal time NOT NULL DEFAULT '00:00:00'::time without time zone,
  starttime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  endtime time NOT NULL DEFAULT '00:00:00'::time without time zone,
  statuscode int2 NOT NULL DEFAULT 0,
  note text NOT NULL DEFAULT ''::text,
  internalinfo varchar(80) NOT NULL DEFAULT ''::character varying,
  id int4 NOT NULL DEFAULT nextval('timereport_id_seq'::text),
  CONSTRAINT "TimeReport_pkey" PRIMARY KEY (id)
)
WITH OIDS;
ALTER TABLE timereport OWNER TO postgres;
[/code]


What I would like to retrieve is all columns from timereport
and the four timereportbreaks connected to the timereport table.
However, since each break is not a separate column I am not
sure how to solve this. And since this database already has data
in it. I can't remodel the table. This is the output I want:

timereport.*, timereportbreak0start, timereportbreak0end,
timereportbreak1start, timereportbreak1end, ...



I did try something like this but using LIMIT makes it faulty and errornous:

[code]
CREATE OR REPLACE FUNCTION timereport_getbydateandemployee("varchar", date,
date, "varchar")
  RETURNS SETOF timereportandbreaks AS
$BODY$
SELECT DISTINCT
  timereport.employeesignature,
  timereport.startdate,
  timereport.workedtimetotal,
  timereport.starttime,
  timereport.endtime,
  timereport.statuscode,
  timereport.note,
  timereport.internalinfo,
  timereport.id
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '0'
           LIMIT '1'
        ) AS break1start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '0'
           LIMIT '1'
        ) AS break1end
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '1'
           LIMIT '1'
        ) AS break2start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '1'
           LIMIT '1'
        ) AS break2end
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '2'
           LIMIT '1'
        ) AS break3start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '2'
           LIMIT '1'
        ) AS break3end
       ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '3'
           LIMIT '1'
        ) AS break4start
       ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport,
employee
           WHERE
             (timereport.startdate BETWEEN $2 AND $3)
             AND
             (upper(timereport.employeesignature) = upper($4))
             AND
             upper(timereport.employeesignature) = upper(employee.signature)
             AND
             employee.companyid = $1
             AND
             timereport.id = timereportbreak.timereportid
             AND
             timereportbreak.indexnumber = '3'
           LIMIT '1'
        ) AS break4end
FROM
  timereport, timereportbreak, employee
WHERE
  (timereport.startdate BETWEEN $2 AND $3)
  AND
  (upper(timereport.employeesignature) = upper($4))
  AND
  (upper(timereport.employeesignature) = upper(employee.signature))
  AND
  (employee.companyid = $1)
  AND
  (timereportbreak.timereportid = timereport.id)
ORDER BY
  timereport.startdate,
  timereport.starttime
$BODY$
  LANGUAGE 'sql' VOLATILE;
ALTER FUNCTION timereport_getbydateandemployee("varchar", date, date,
"varchar") OWNER TO postgres;
[/code]



Any help would be tremendously appretiated.

// Kind Regards Robert