Thread: stored procedure problem: parameters not properly passed

stored procedure problem: parameters not properly passed

From
shadowbox
Date:
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,

Re: stored procedure problem: parameters not properly passed

From
"Lane Van Ingen"
Date:
There are several ways I have done this, but here is one ....

CREATE OR REPLACE FUNCTION constantText(varchar, int4)
  RETURNS varchar AS
$BODY$

DECLARE
  -- txtvalue tells what domain of constants are being requested
  txtvalue ALIAS FOR $1;
  intValue ALIAS FOR $2;

BEGIN

  -- for use on any new modules requiring color translations
IF txtvalue = 'adns_std_colors' THEN
  IF intValue = -1 THEN
    RETURN 'WHITE';
  END IF;
  :
  :

-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]On Behalf Of shadowbox
Sent: Wednesday, September 28, 2005 8:00 AM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] stored procedure problem: parameters not properly
passed


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,

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster


Re: stored procedure problem: parameters not properly passed

From
shadowbox
Date:
after experimenting on the stored procs, i noticed that i need to wrap
the SQL statements in single quotes (and these single quotes used in
the SQL should be escaped), like so:

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;

    EXECUTE '
    CREATE OR REPLACE 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';

or are there more ways to do this?

thanks!


On 9/28/05, shadowbox <percy.deleon@gmail.com> wrote:
> 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,
>