Thread: Passing TEXT variable to a function
Hello.
I have a function that creates a long TEXT string that looks like this:
30686,30687,30683,30689,30703,30677,30678,30707,30701,30680,30694,30697,30692,30700,30699,30709,30698,30713,30684,30691,30706,30704,30688,30710,30685,30708,30696,30695,30679,30681,30690,30702,30712,30693,30711,30682,30705,30726,30728,30729,31885,31886,31887,30567,30568,30569,30570,30571,30572,30573,30574,30575,30576,30577,30578,30579,30580,30581,30582,30583,30584,30585,30586,30587,30588,30589,30590,30591,30592,30593,30594,30595,30596,30597,30598,30599,30600,30601,30896,30903,30943,30945,30524,30893,30894,30895,30897,30898,30899,30900,30901,30902,30904,30905,30906,30907,30908,30909,30910,30911,30912,30913,30914,30915,30916,30917,30918,30919,30920,30921,30922,30923,30924,30925,30926,30927,30928,30929,30930,30931,30932,30933,30934,30935,30936,30937,30938,30939,30940,30941,30942,30944,30946,30947,30948,30949,30950,30951,30952,30953
The variable is defined as TEXT, and I'm trying to pass it to a function that is setup to accept a TEXT parameter.
Here is the call:
FOR getpercentiles IN EXECUTE ''SELECT * FROM get_facility_percentiles(''||wagerateid_list||'')'' LOOP
-- RAISE NOTICE ''DONE CALLING FUNCTION '';
END LOOP;
Here is the function declaration:
CREATE OR REPLACE FUNCTION get_facility_percentiles(TEXT) RETURNS public."percentiles" AS '
The problem is that I get this error:
WARNING: Error occurred while executing PL/pgSQL function calc_sum_facilities
WARNING: line 189 at for over execute statement
ERROR: Cannot pass more than 32 arguments to a function
So, it thinks I'm passing separate parameters.
I'm not sure what to do about this. If I have to put double quotes around it, I will need to strip them on the other
sidebecause the called function is using the TEXT string in a query like this:
select * from table where id in (TEXT);
Any help would be greatly appreciated!
Thank you in advance,
Best Regards,
--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38
Betsy Barker <betsy.barker@supportservicesinc.com> writes:
> Here is the call:
> FOR getpercentiles IN EXECUTE ''SELECT * FROM get_facility_percentiles(''||wagerateid_list||'')'' LOOP
> -- RAISE NOTICE ''DONE CALLING FUNCTION '';
> END LOOP;
You want something like
FOR getpercentiles IN EXECUTE ''SELECT * FROM get_facility_percentiles(''||quote_literal(wagerateid_list)||'')''
LOOP
regards, tom lane
Betsy,
> The variable is defined as TEXT, and I'm trying to pass it to a function
> that is setup to accept a TEXT parameter. Here is the call:
First off, let me say that you may be better off using a different strategy
for this. But I don't know much about your application, so you may not.
> FOR getpercentiles IN EXECUTE ''SELECT * FROM
> get_facility_percentiles(''||wagerateid_list||'')'' LOOP -- RAISE
> NOTICE ''DONE CALLING FUNCTION '';
Aha, syntax error, you forgot the nested quotes:
FOR getpercentiles IN EXECUTE ''SELECT * FROM get_facility_percentiles(''''''
|| wagerateid_list || '''''')'' LOOP
FWIW, version 8.0 will have a feature to do away with the nested-quote-mania.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Aha, syntax error, you forgot the nested quotes:
> FOR getpercentiles IN EXECUTE ''SELECT * FROM get_facility_percentiles(''''''
> || wagerateid_list || '''''')'' LOOP
She really wants quote_literal(), instead, so that the function doesn't
break if someone passes a value that contains quotes ...
regards, tom lane
quote_literal() worked. Thank you both!
On Tue, 24 Aug 2004 17:33:08 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Josh Berkus <josh@agliodbs.com> writes:
> > Aha, syntax error, you forgot the nested quotes:
>
> > FOR getpercentiles IN EXECUTE ''SELECT * FROM get_facility_percentiles(''''''
> > || wagerateid_list || '''''')'' LOOP
>
> She really wants quote_literal(), instead, so that the function doesn't
> break if someone passes a value that contains quotes ...
>
> regards, tom lane
>
--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38