Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dynamic binding issue

Dynamic binding issue

From
Lorusso Domenico
Date:
Hello guys,
I'm a problem with dynamic sql.
I am trying to write a generic function that is able to read and update a table based on some data coming from e previous record.
Here the example
_sqlStr=format('select *
  from  %1$s.%2$s
  where (' || array_to_string(_activeRec.pk_columns_list, ',') || ') in (select ' ||
  'row($1[''' || array_to_string(_activeRec.pk_columns_list, '''],$1[''') || ''']))'
  , _activeRec.name_of_schema, _activeRec.main_table);

execute _sqlStr using oldRec into _rec;

My problem is oldRec is a type record, so the substitution performed by execute fails, because it can't recognize the field if the variable is record and not a specific composite record type.

I suppose this is a recurrent question, but I can't find a solution...

--
Domenico L.

Re: Dynamic binding issue

From
Adrian Klaver
Date:
On 6/12/23 15:13, Lorusso Domenico wrote:
> Hello guys,
> I'm a problem with dynamic sql.
> I am trying to write a generic function that is able to read and update 
> a table based on some data coming from e previous record.
> Here the example
> _sqlStr=format('select *
>    from  %1$s.%2$s
>    where (' || array_to_string(_activeRec.pk_columns_list, ',') || ') in 
> (select ' ||
>    'row($1[''' || array_to_string(_activeRec.pk_columns_list, 
> '''],$1[''') || ''']))'
>    , _activeRec.name_of_schema, _activeRec.main_table);
> 
> execute _sqlStr using oldRec into _rec;
> 
> My problem is oldRec is a type record, so the substitution performed by 
> execute fails, because it can't recognize the field if the variable is 
> record and not a specific composite record type.

Why not use a row type?:

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES

> 
> I suppose this is a recurrent question, but I can't find a solution...
> 
> -- 
> Domenico L.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Dynamic binding issue

From
Lorusso Domenico
Date:
Because the function is general and should do the same thing for many different table.
Unique constraint, each table must have a field with a specific type 

Il mar 13 giu 2023, 01:31 Adrian Klaver <adrian.klaver@aklaver.com> ha scritto:
On 6/12/23 15:13, Lorusso Domenico wrote:
> Hello guys,
> I'm a problem with dynamic sql.
> I am trying to write a generic function that is able to read and update
> a table based on some data coming from e previous record.
> Here the example
> _sqlStr=format('select *
>    from  %1$s.%2$s
>    where (' || array_to_string(_activeRec.pk_columns_list, ',') || ') in
> (select ' ||
>    'row($1[''' || array_to_string(_activeRec.pk_columns_list,
> '''],$1[''') || ''']))'
>    , _activeRec.name_of_schema, _activeRec.main_table);
>
> execute _sqlStr using oldRec into _rec;
>
> My problem is oldRec is a type record, so the substitution performed by
> execute fails, because it can't recognize the field if the variable is
> record and not a specific composite record type.

Why not use a row type?:

https://www.postgresql.org/docs/current/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES

>
> I suppose this is a recurrent question, but I can't find a solution...
>
> --
> Domenico L.

--
Adrian Klaver
adrian.klaver@aklaver.com