Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    28

    Unanswered: Column Definitions

    Ok...still a newbie at PostgreSQL here.

    I'm using pgAdmin III and I created a function so that I can create a crystal report against it.


    Code:
    CREATE OR REPLACE FUNCTION http_user_report("IP" text, "FROM" timestamp without time zone, "TO" timestamp without time zone)
      RETURNS record AS
    $BODY$SELECT
    src_ip,
    dst_ip,
    info_3 AS user,
    info_5 AS url,
    info_6 AS details,
    to_date(update_time, 'YYYY-MM-DD') AS date,
    to_char(update_time, 'HH24:MI'),
    update_time
    
    FROM
    traffic_table_9097008431d08
    
    WHERE
    src_ip = $1 AND
    update_time BETWEEN $2 AND $3$BODY$
      LANGUAGE 'sql' STABLE;
    ALTER FUNCTION http_user_report(text, timestamp without time zone, timestamp without time zone) OWNER TO wguser;
    This returns a the following error message:
    ERROR: a column definition list is required for functions returning "record"

    I'm assuming that I have to define the output columns somehow. What is the best method for this? I'm really struggling with the syntax and I can't seem to find proper documentation for functions.

    Thanks,
    Last edited by loquin; 09-10-08 at 12:09. Reason: added [code] [/code] tags

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    try this (untested) ...

    Code:
    CREATE OR REPLACE FUNCTION http_user_report("IP" text, "FROM" timestamp without time zone, "TO" timestamp without time zone)
      RETURNS record AS
    $BODY$
    DECLARE TRec Record;
    
    SELECT Into TRec 
    src_ip,
    dst_ip,
    info_3 AS user,
    info_5 AS url,
    info_6 AS details,
    to_date(update_time, 'YYYY-MM-DD') AS date,
    to_char(update_time, 'HH24:MI'),
    update_time
    
    FROM
    traffic_table_9097008431d08
    
    WHERE
    src_ip = $1 AND
    update_time BETWEEN $2 AND $3;
    
    Return TRec;
    
    $BODY$
      LANGUAGE 'sql' STABLE;
    ALTER FUNCTION http_user_report(text, timestamp without time zone, timestamp without time zone) OWNER TO wguser;
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2008
    Posts
    28
    I'm afraid that didn't work...I received ->

    ERROR: syntax error at or near "record"
    LINE 8: DECLARE trec record;
    ^

    Isn't record a valid type?

  4. #4
    Join Date
    Apr 2008
    Posts
    29
    Quote Originally Posted by YBeauchamps

    I'm assuming that I have to define the output columns somehow. What is the best method for this? I'm really struggling with the syntax and I can't seem to find proper documentation for functions.

    Thanks,
    One way you could define the record for that function is with the help of OUT parameters (change type_of_something for the correct data type):

    Code:
    CREATE OR REPLACE FUNCTION http_user_report("IP" text, "FROM" timestamp without time zone, "TO" timestamp without time zone, OUT src_ip type_of_src_ip, OUT dst_ip type_of_dst_ip, OUT user type_of_info_3, OUT url type_of_info_5, ...)
      RETURNS SETOF record AS
    $BODY$
    (...)
    Notice also the use of RETURNS SETOF

    HTH

  5. #5
    Join Date
    Mar 2008
    Posts
    28
    Thanks for the help.

    However I just got a little impatient. Once I found a proper PostgreSQL ODBC driver for Crystal and made a successful connection; I just wrote the sql code directly in Crystal. It's not exactly how I prefer to run reports but it gave me less headaches that way!

Posting Permissions

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