SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
Hey, hey.
Better :
SELECT blablah FROM providers WHERE uid = child_provider LIMIT 1;
IF NOT FOUND THEN exit with error ELSE do your stuff
Why scan more than 1 row when you just need existence
I had to fiddle a bit more, but I did finally get it to work. Thanks
Mike
CREATE OR REPLACE FUNCTION svp_getparentproviderids (INTEGER)
RETURNS SETOF INTEGER
AS '
DECLARE
child_provider ALIAS FOR $1;
cid INTEGER;
BEGIN
SELECT INTO cid count(*) FROM providers WHERE uid =child_provider;
Sorry about that... try this:
CREATE OR REPLACE FUNCTION svp_getparentproviderids
(INTEGER)
RETURNS SETOF svp_getparentproviderids_uid_type
AS '
DECLARE
child_provider ALIAS FOR $1;
cid svp_getparentproviderids_uid_type%ROWTYPE;
tmp_cid INTEGER;
BEGIN
SELECT INTO tmp_cid count(*) FROM pr
I have been fiddling with what you sent. I have it working mostly, save
for I keep getting syntax errors on the "RETURN NEXT cid;" line. If I
remove this line then the function works ( but returns nothing of
course). Any ideas on why the RETURN NEXT doesn't like the variable as
a parameter?
sp_
Forgot one line. See below
On Mon, 22 Nov 2004 11:54:30 -0500, Mike Rylander <[EMAIL PROTECTED]> wrote:
> I'm feeling sausey today, so here is my (untested) attempt to
[snip]
> > CREATE FUNCTION svp_getparentproviderids (@child_provider INTEGER)
> > RETURNS @provider_ids TABLE ( uid INTEGER )
> >
I'm feeling sausey today, so here is my (untested) attempt to
translate your function. It's inline below, and you'll want to look
here http://www.postgresql.org/docs/7.4/interactive/plpgsql.html for
more information.
On Mon, 22 Nov 2004 09:18:13 -0600, Richard Rowell
<[EMAIL PROTECTED]> wrote:
>
I'm trying to port some TSQL to PLPGSQL. The DB has a table with a
recursive foreign key that represents a tree hierarchy. I'm trying to
re-create a TSQL function that pulls out all the ancestors of a given
node in the hierarchy.
I'm rather new to PLSQL and I have several questions.
1. In TSQL
