Help with SQL Function
I sent this message yesterday, but it doesn't appear to have made it to the list.
Apologies to anyone who receives it twice.
I'm falling at the first hurdle. Can someone tell me how to pass an attribute value into a function, as I'm trying to do below? I've studied the docs every which way, but can't seem to find the cause of my problem.
Thanks in advance for ignorance relief.
extracts=# create function testfunc(text) returns int4 as '
extracts'# select count (*) from dedcolo where equip_type = ''$1'' --- That's doubled single quotes
extracts'# ' language 'sql';
CREATE
extracts=# select testfunc('Dialup');
testfunc
----------
0
(1 row)
extracts=# create function testfunc() returns int4 as '
extracts'# select count (*) from dedcolo where equip_type = ''Dialup'' --- Doubled single quotes, again
extracts'# ' language 'sql';
CREATE
extracts=# select testfunc();
testfunc
----------
3453
(1 row)
Jeff Eckermann <jeff@akira.eckermann.com> writes:
extracts=# create function testfunc(text) returns int4 as '
extracts'# select count (*) from dedcolo where equip_type = ''$1'' --- That's doubled single quotes
extracts'# ' language 'sql';
CREATE
That's looking for rows where equip_type = '$1' ... ie, the literal
string $1. What you probably wanted is
create function testfunc(text) returns int4 as '
select count (*) from dedcolo where equip_type = $1
' language 'sql';
regards, tom lane
Thanks, Tom, and also to Alex Pilosov for his answer.
I was extrapolating from the plpgsql docs, which I probably didn't
understand correctly.
Programming By Example (which is what we non-programmers are obliged to do)
doesn't work so well when the docs are somewhat sparse.
Are there any plans to expand the docs on plpgsql, since there are new
features coming? Plpgsql looks like a good tool for enhancing my
development of financial etc reports, but in working with it, I've had two
weeks of frustration and not much of anything else so far.
Show quoted text
-----Original Message-----
From: Tom Lane [SMTP:tgl@sss.pgh.pa.us]
Sent: Saturday, January 06, 2001 3:41 PM
To: Jeff Eckermann
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Help with SQL FunctionJeff Eckermann <jeff@akira.eckermann.com> writes:
extracts=# create function testfunc(text) returns int4 as '
extracts'# select count (*) from dedcolo where equip_type = ''$1''--- That's doubled single quotesextracts'# ' language 'sql';
CREATEThat's looking for rows where equip_type = '$1' ... ie, the literal
string $1. What you probably wanted iscreate function testfunc(text) returns int4 as '
select count (*) from dedcolo where equip_type = $1
' language 'sql';regards, tom lane
Import Notes
Resolved by subject fallback