From: | "Lane Van Ingen" <lvaningen(at)esncc(dot)com> |
---|---|
To: | "shadowbox" <percy(dot)deleon(at)gmail(dot)com>, <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: stored procedure problem: parameters not properly passed |
Date: | 2005-09-28 13:02:00 |
Message-ID: | EKEMKEFLOMKDDLIALABIIEAPCDAA.lvaningen@esncc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)postgresql(dot)org
[mailto:pgsql-novice-owner(at)postgresql(dot)org]On Behalf Of shadowbox
Sent: Wednesday, September 28, 2005 8:00 AM
To: pgsql-novice(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Eradict | 2005-09-28 14:27:09 | createlang issue in XP |
Previous Message | shadowbox | 2005-09-28 11:59:53 | stored procedure problem: parameters not properly passed |