CODEWITHSUNDEEP
functionpostgresqlcrosstab
Tony Staley
Tony Staley

Reputation: 11

Table function throwing ERROR: there is no parameter $1

I have the following query which I'd like to turn into a function. I have written the SQL for 1 company_id = 26304

SELECT t.* FROM crosstab(
   $$
    select company_id, row_number() OVER (ORDER BY year DESC) AS rn2, id
    from 
    (select i.company_id, year, f.id, f.created_at,
                 row_number() OVER (PARTITION BY year
                                ORDER BY year DESC, f.created_at DESC NULLS LAST) AS rn
    from
    public.interactions i
    inner join public.financials f on f.interaction_id = i.id
    where company_id = 26304
) t1
    where rn= 1 limit 3
   $$
   ) AS t (company_id int, financial_id_1 int, financial_id_2 int, financial_id_3 int);

This SQL statement pivots my dataset and returns the following as expected:

company_id  financial_id_1  financial_id_2  financial_id_3
26304       6796            6795            6786

However, when I try to turn this into a table function it throws the following error:

CREATE FUNCTION public.returnfinancials (int4) RETURNS TABLE (company_id int, financial_id_1 int, financial_id_2 int, financial_id_3 int) 
as
$$
SELECT t.* FROM crosstab(
   '
    select company_id, row_number() OVER (ORDER BY year DESC) AS rn2, id
    from 
    (select i.company_id, year, f.id, f.created_at,
            row_number() OVER (PARTITION BY year ORDER BY year DESC, f.created_at DESC NULLS LAST) AS rn
    from
    public.interactions i
    inner join public.financials f on f.interaction_id = i.id
    where company_id =  $1
) t1
    where rn= 1 limit 3
   '
   ) AS t (company_id int, financial_id_1 int, financial_id_2 int, financial_id_3 int);
$$
LANGUAGE 'sql'

Call:

select * from returnfinancials(23)

Throws:

ERROR: there is no parameter $1
 Where: SQL function "returnfinancials" statement 1
Line: 1

Upvotes: 1

Views: 3486

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324771

The issue is that $1 is within a text string. It's interpreted by crosstab as a query, but within the context of the crosstab function call the parameters to the calling function are not visible. As crosstab its self passes no parameters to the query when it executes it, you get an error.

To solve this you should substitute the parameter value into the query string you pass to crosstab.

In your specific case it's OK to just substitute directly because the parameter is an integer:

'.... where company_id = '||$1||' .... '

but in general you should be careful about SQL injection and it's cleanest and safest to consistently quote your parameters. Note that '11' is a valid integer literal in SQL; it's always legal to quote an identifier, it's just optional for numbers.

So instead, even for numbers, I suggest that you use:

'.... where company_id = '||quote_literal($1)||' ....'

or use the format function to construct the string:

format('.... where company_id = %L ....', $1)

That way if someone later changes company_id to a non-numeric type you don't get a pretty SQL injection hole.

Upvotes: 2

Houari
Houari

Reputation: 5641

Try replace 

where company_id = $1

by 

where company_id = '||$1||'

Upvotes: 0

Related Questions