Thread: Passing TEXT variable to a function

Passing TEXT variable to a function

From
Betsy Barker
Date:
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

Re: Passing TEXT variable to a function

From
Tom Lane
Date:
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

Re: Passing TEXT variable to a function

From
Josh Berkus
Date:
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

Re: Passing TEXT variable to a function

From
Tom Lane
Date:
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

Re: Passing TEXT variable to a function

From
Betsy Barker
Date:
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