Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    10

    Unanswered: Function (sql) returning an empty row

    Hello,

    I have the following tables..

    Code:
    CREATE TABLE timereport
    (
      employeesignature varchar(6)
      startdate date,
      workedtimetotal time
      starttime time
      endtime time
      statuscode int2
      note text
      internalinfo varchar(80) 
      id int4 
    ) 
    
    CREATE TABLE timereportbreak
    (
      timereportid int4 
      starttime time 
      endtime time 
      indexnumber int4 
    )

    Each timereport is connected to four breaks in table
    timereportbreak.

    Now, this is the function i use to retrieve the data..

    Code:
    CREATE OR REPLACE FUNCTION timereport_getbyid(int4)
      RETURNS timereportandbreaks AS
    $BODY$
    SELECT timereport.*
           ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '0'
               LIMIT '1'
            ) AS break1start
           ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '0'
               LIMIT '1'
            ) AS break1end
           ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '1'
               LIMIT '1'
            ) AS break2start
           ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '1'
               LIMIT '1'
            ) AS break2end
           ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '2'
               LIMIT '1'
            ) AS break3start
           ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '2'
               LIMIT '1'
            ) AS break3end
           ,(SELECT timereportbreak.starttime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '3'
               LIMIT '1'
            ) AS break4start
           ,(SELECT timereportbreak.endtime FROM timereportbreak, timereport
               WHERE 
                 timereport.id = $1
                 AND
                 timereportbreak.timereportid = timereport.id
                 AND
                 timereportbreak.indexnumber = '3'
               LIMIT '1'
            ) AS break4end
      FROM timereport
      WHERE
        ( timereport.id = $1)
        AND
        ( timereportbreak.timereportid = timereport.id )
      ORDER BY timereport.startdate, timereport.starttime
    $BODY$
      LANGUAGE 'sql' VOLATILE;
    ALTER FUNCTION timereport_getbyid(int4) OWNER TO postgres;
    The function works as it should, except for when I enter an id
    into the function that doesn't exist. Then the function returns an
    empty row. I would like to see no row at all.

    If I extract the SQL part and run it outside of the function no
    row is returned.

    Any help is appreciated,
    Cheers!

  2. #2
    Join Date
    Sep 2005
    Location
    Sweden
    Posts
    10
    As far as I can tell this is normal behaviour when
    not returning a SET.

    Using "RETURNS SETOF timereportandbreaks AS"
    Solves it.


    Cheers.

Posting Permissions

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