Skip site navigation (1) Skip section navigation (2)

stored procedure problem: parameters not properly passed

From: shadowbox <percy(dot)deleon(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: stored procedure problem: parameters not properly passed
Date: 2005-09-28 11:59:53
Message-ID: e699f20505092804595c44d9c1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
hi list,

i'm relatively new in creating stored procs and i'm having this problem:

i created a stored proc that creates an aggregated view and it  was
successfully loaded without errors. my problem was, it seems that the
parameters were not successfully passed to the procedure. anything i
missed? attached is the stored procedure and the definition of the
created view:

--- snip ---
CREATE OR REPLACE FUNCTION
    make_etms_views(start_date date, end_date date)
RETURNS void
AS $$
DECLARE

BEGIN
    raise notice 'Start Date: %', start_date;
    raise notice 'End Date: %', end_date;
    CREATE VIEW hours_worked AS
    SELECT id, b.name || ', ' || b.nickname AS resource,
        sum(EXTRACT (EPOCH FROM a.hours_actual)/3600) AS hours_worked,
        sum(a.hours_actual) as days_worked
    FROM t_timesheet a, t_resource b
    WHERE activity_id NOT IN
        ( SELECT id
            FROM t_project_activity
            WHERE name ILIKE '%leave')
    AND  a.resource_id = b.id
    AND a.date_sheet BETWEEN start_date AND end_date
    GROUP BY id, resource
    ORDER BY resource
    ;
    RETURN void;

END;
$$
LANGUAGE 'plpgsql';

View definition (you'll notice that the date_sheet params were not not
properly passed, hence the $1 and $2) :

 SELECT b.id, (b.name::text || ', '::text) || b.nickname::text AS
resource, sum(date_part('epoch'::text, a.hours_actual) / 3600::double
precision) AS hours_worked, sum(a.hours_actual) AS days_worked
   FROM t_timesheet a, t_resource b
  WHERE NOT (a.activity_id IN ( SELECT t_project_activity.id
           FROM t_project_activity
          WHERE t_project_activity.name::text ~~* '%leave'::text)) AND
a.resource_id::text = b.id::text AND a.date_sheet >= $1 AND
a.date_sheet <= $2
  GROUP BY b.id, (b.name::text || ', '::text) || b.nickname::text
  ORDER BY (b.name::text || ', '::text) || b.nickname::text;
t;


i already checked out the docs, but i'm not successful in gettnig
substantial info on the problem. anyways, any help from you guys will
be appreciated.

thanks,

Responses

pgsql-novice by date

Next:From: Lane Van IngenDate: 2005-09-28 13:02:00
Subject: Re: stored procedure problem: parameters not properly passed
Previous:From: Ennio-SrDate: 2005-09-27 20:04:35
Subject: right align (justify) to_char outcome in a column

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group