CODEWITHSUNDEEP
postgresqlstored-procedures
mark li
mark li

Reputation: 347

how to call postgresql stored procs from inside another stored proc and include return values in queries

I have a postgresql function / stored proc that does the following: 1. calls another function and saves the value into a variable. 2. executes another sql statement using the value I got from step one as an argument.

My problem is that the query is not returning any data. No errors are returned either. I'm just new to postgresql so I don't know the best way to debug... but I added a RAISE NOTICE command right after step 1, like so: 

SELECT INTO active_id get_widget_id(widget_desc);
RAISE NOTICE 'Active ID is:(%)', active_id;

In the "Messages" section of the pgadmin3 screen, I see the debug message with the data:

NOTICE: Active ID is:(2)

I'm wondering whether or not the brackets are causing the problem for me.
Here's the sql I'm trying to run in step 2: 

        SELECT d.id, d.contact_id, d.priority, cp.contact
        FROM widget_details d, contact_profile cp, contact_type ct
        WHERE d.rule_id=active_id
        AND d.active_yn = 't'
        AND cp.id=d.contact_id
        AND cp.contact_type_id=ct.id
        AND ct.name = 'email'
        Order by d.priority ASC

You'll notice that in my where clause I am referencing the variable "active_id". I know that this query should return at least one row because when i run a straight sql select (vs using this function) and substitute the value 2 for the variable "active_id", I get back the data I'm looking for.

Any suggetions would be appreciated.

Thanks.

EDIT 1:

Here's the full function definition:

CREATE TYPE custom_return_type AS (
    widgetnum integer,
    contactid integer,
    priority integer,
    contactdetails character varying
);

CREATE OR REPLACE FUNCTION test(widget_desc integer)
  RETURNS SETOF custom_return_type AS
$BODY$
DECLARE 
     active_id integer; 
     rec custom_return_type ;

BEGIN
SELECT INTO active_id get_widget_id(widget_desc);
    RAISE NOTICE 'Active ID is:(%)', active_id;

FOR rec IN 
    SELECT d.id, d.contact_id, d.priority, cp.contact
        FROM widget_details d, contact_profile cp, contact_type ct
        WHERE d.rule_id=active_id
        AND d.active_yn = 't'
        AND cp.id=d.contact_id
        AND cp.contact_type_id=ct.id
        AND ct.name = 'email'
        Order by d.priority ASC
    LOOP
     RETURN NEXT rec;
END LOOP;   

 END
 $BODY$

Upvotes: 1

Views: 6410

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324345

That's several levels of too-complicated (edit: as it turns out that Erwin already explained to you last time you posted the same thing). Start by using RETURNS TABLE and RETURN QUERY:

CREATE OR REPLACE FUNCTION test(fmfm_number integer)
RETURNS TABLE ( 
    widgetnum integer,
    contactid integer,
    priority integer,
    contactdetails character varying
) AS
$BODY$
BEGIN
    RETURN QUERY SELECT d.id, d.contact_id, d.priority, cp.contact
        FROM widget_details d, contact_profile cp, contact_type ct
        WHERE d.rule_id = get_widget_id(widget_desc)
        AND d.active_yn = 't'
        AND cp.id=d.contact_id
        AND cp.contact_type_id=ct.id
        AND ct.name = 'email'
        Order by d.priority ASC;
 END
 $BODY$ LANGUAGE plpgsql;

at which point it's probably simple enough to be turned into a trivial SQL function or even a view. Hard to be sure, since the function doesn't make tons of sense as written:

  • You never use the parameter fmfm_number anywhere; and
  • widget_desc is never defined

so this function could never run. Clearly you haven't shown us the real source code, but some kind of "simplified" code that doesn't match the code you're really having issues with.

Upvotes: 1

user2478690
user2478690

Reputation: 441

There is a difference between:
SELECT INTO ...
[http://www.postgresql.org/docs/current/interactive/sql-selectinto.html]
and
SELECT select_expressions INTO [STRICT] target FROM ...;
[http://www.postgresql.org/docs/current/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-ONEROW]

I think you want:
SELECT get_widget_id(widget_desc) INTO active_id;

Upvotes: 0

Related Questions