Re: [SQL] postgres subfunction return error

[jonathansfl]

I'm trying to pass a REFCURSOR variable from a subfunction to its parent
calling function, who will then pass it to the user (for parsing).
thanks to David J I fixed it somewhat, but the user now receives the TEXT of
 (etc.) instead of the actual data in that REFCURSOR
variable.

I think the problem is with the "OPEN swv_refcur for SELECT v_outvar;" which
is not returning the REFCURSOR's actual data.
thank you for your help!!
Jonathan

NEW PARENT FUNCTION CODE:
[CODE]
CREATE OR REPLACE FUNCTION custom.pr_test_parentfunction (
  v_action varchar,
  out swv_refcur refcursor,
  out swv_refcur2 refcursor,
  out swv_refcur3 refcursor
)
RETURNS record AS
$body$
DECLARE
   SWV_Action VARCHAR(50) DEFAULT Coalesce(v_Action,'1');
   v_outvar1 REFCURSOR; v_outvar2 REFCURSOR; v_outvar3 REFCURSOR;
BEGIN
SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM
custom.pr_test_subfunction(SWV_Action);
OPEN swv_refcur  for SELECT v_outvar1;
OPEN swv_refcur2 for SELECT v_outvar2;
OPEN swv_refcur3 for SELECT v_outvar3;
RETURN;
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
[/CODE]



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772613.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Can I simplify this somehow?

[Larry Rosenman]

I tried(!) to write this as a with (CTE), but failed.

Can one of the CTE experts (or better SQL writer) help me here?

-- generate a table of timestamps to match against
select
generate_series(date_trunc('day',now()-'45 days'::interval),now()+'1 
hour'::inte

rval,'1 hour')
   AS thetime  into temp table timestamps;

-- get a count of logged in users for a particular time
SELECT thetime,case extract(dow  from thetime)
   when 0 then 'Sunday'
   when 1 then 'Monday'
   when 2 then 'Tuesday'
   when 3 then 'Wednesday'
   when 4 then 'Thursday'
   when 5 then 'Friday'
   when 6 then 'Saturday' end AS "Day", count(*) AS 
"#LoggedIn"

FROM  timestamps,user_session
WHERE thetime BETWEEN login_time AND COALESCE(logout_time, now())
GROUP BY thetime
ORDER BY thetime;

Thanks for any help at all.


--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org
US Mail: 108 Turvey Cove, Hutto, TX 78634-5688


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] postgres subfunction return error

[David Johnston]

jonathansfl wrote
> SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM
> custom.pr_test_subfunction(SWV_Action);
> OPEN swv_refcur  for SELECT v_outvar1;
> OPEN swv_refcur2 for SELECT v_outvar2;
> OPEN swv_refcur3 for SELECT v_outvar3;
> RETURN;

I've never used cursors in this way so my help is more theory but:

The called-function already created the cursors.  In the parent function you
should simply be able to pass them through unaltered:

SELECT * INTO v_outvar1, ...;
swv_refcur := v_outvar1;
...
...
RETURN;

You can possible simply the above and toss the temporary variables but that
should not impact the semantics.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772627.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Can I simplify this somehow?

[Adam Jelinek]

Are you looking for something like this for the result for the last 45 days
or something else?

TIME  MON   TUE  WED ...
09:00 13   4
10:00 50   8


On Fri, Sep 27, 2013 at 10:22 AM, Larry Rosenman  wrote:

> I tried(!) to write this as a with (CTE), but failed.
>
> Can one of the CTE experts (or better SQL writer) help me here?
>
> -- generate a table of timestamps to match against
> select
> generate_series(date_trunc('**day',now()-'45 days'::interval),now()+'1
> hour'::inte
> rval,'1 hour')
>AS thetime  into temp table timestamps;
>
> -- get a count of logged in users for a particular time
> SELECT thetime,case extract(dow  from thetime)
>when 0 then 'Sunday'
>when 1 then 'Monday'
>when 2 then 'Tuesday'
>when 3 then 'Wednesday'
>when 4 then 'Thursday'
>when 5 then 'Friday'
>when 6 then 'Saturday' end AS "Day", count(*) AS "#LoggedIn"
> FROM  timestamps,user_session
> WHERE thetime BETWEEN login_time AND COALESCE(logout_time, now())
> GROUP BY thetime
> ORDER BY thetime;
>
> Thanks for any help at all.
>
>
> --
> Larry Rosenman http://www.lerctr.org/~ler
> Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org
> US Mail: 108 Turvey Cove, Hutto, TX 78634-5688
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-sql
>

Re: [SQL] Can I simplify this somehow?

[Larry Rosenman]

On 2013-09-27 14:24, Adam Jelinek wrote:

Are you looking for something like this for the result for the last 45
days or something else?

TIME  MON   TUE  WED ...
09:00     1        3       4
10:00     5        0       8


That would be cool, but just a list is good too.



--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 214-642-9640 (c) E-Mail: l...@lerctr.org
US Mail: 108 Turvey Cove, Hutto, TX 78634-5688


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] postgres subfunction return error

[jonathansfl]

Hurray, that works!

Many thanks David

 

CREATE OR REPLACE FUNCTION custom.pr_test_parentfunction (

  v_action varchar,

  out swv_refcur refcursor,

  out swv_refcur2 refcursor,

  out swv_refcur3 refcursor

)

RETURNS record AS

$body$

DECLARE

   SWV_Action VARCHAR(50) DEFAULT Coalesce(v_Action,'1');

   v_outvar1 REFCURSOR; v_outvar2 REFCURSOR; v_outvar3 REFCURSOR;

BEGIN

SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM
custom.pr_test_subfunction(SWV_Action);



swv_refcur   = v_outvar1;

swv_refcur2  = v_outvar2;

swv_refcur3  = v_outvar3;

 

RETURN;

END;

$body$

LANGUAGE 'plpgsql'

VOLATILE

CALLED ON NULL INPUT

SECURITY INVOKER

COST 100;

 

From: David Johnston [via PostgreSQL]
[mailto:ml-node+s1045698n5772627...@n5.nabble.com] 
Sent: Friday, September 27, 2013 1:43 PM
To: jonathansfl
Subject: Re: postgres subfunction return error

 

jonathansfl wrote

SELECT * INTO v_outvar1, v_outvar2, v_outvar3 FROM
custom.pr_test_subfunction(SWV_Action); 
OPEN swv_refcur  for SELECT v_outvar1; 
OPEN swv_refcur2 for SELECT v_outvar2; 
OPEN swv_refcur3 for SELECT v_outvar3; 
RETURN; 

I've never used cursors in this way so my help is more theory but: 

The called-function already created the cursors.  In the parent function you
should simply be able to pass them through unaltered: 

SELECT * INTO v_outvar1, ...; 
swv_refcur := v_outvar1; 
... 
... 
RETURN; 

You can possible simply the above and toss the temporary variables but that
should not impact the semantics. 

David J. 



  _  

If you reply to this email, your message will be added to the discussion
below:

http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp
5772407p5772627.html 

To unsubscribe from postgres subfunction return error, click here
 .
 

NAML 





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/postgres-subfunction-return-error-tp5772407p5772658.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

Re: [SQL] Can I simplify this somehow?

[Sergey Konoplev]

On Fri, Sep 27, 2013 at 12:32 PM, Larry Rosenman  wrote:
> On 2013-09-27 14:24, Adam Jelinek wrote:
>> TIME  MON   TUE  WED ...
>> 09:00 13   4
>> 10:00 50   8
>>
> That would be cool, but just a list is good too.

You need to take a look at this module:

http://www.postgresql.org/docs/9.3/static/tablefunc.html

Particularly look at the crostab(text, text) function.

-- 
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp
+1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979
gray...@gmail.com


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql