TransWikia.com

passing variable values to procedure in postgresql via psql

Database Administrators Asked by Aditya Bhardwaj on December 19, 2020

basically I have written a code to pass variable values via psql to my procedure is it supported?
and if supported any pointers to get it done : my code looks like below :

do $$
<<main_block>>
declare
  err_msg text;
BEGIN
        insert into mig.qb_items (qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id  , qb_expense_account_id , purchase_description  , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date  , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level, created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, company_id )
select qb_id , sync_token , qb_updated_at , qb_created_at , qb_expired_at , name , sku , description , active , qb_parent_id , taxable , unit_price , transaction_type , category_type , qb_income_account_id  , qb_expense_account_id , purchase_description  , tax_included , purchase_cost , qb_asset_account_id , track_quantity , inventory_start_date  , quantity_on_hand , qb_sales_tax_id , qb_tax_code_id , abatement_rate , reverse_charge_rate , service_type , level , created_at , updated_at , expired_at , ancestor_id , signature , financial_signature , qb_object , print_grouped_items, :tenant_id as company_id from :schema.qb_items;
update mig.migration_status set status='COMPLETED' WHERE schema=:tenant and table_name='qb_items';
EXCEPTION WHEN others THEN
            GET STACKED DIAGNOSTICS err_msg = MESSAGE_TEXT;
            update mig.migration_status set status='FAILED' WHERE schema=:tenant and table_name='qb_items';
raise notice 'EXCEPTION %', err_msg;
END main_block;
$$;

bt i am getting error as below :

psql:/home/ec2-user/scripts/qb_items.sql:14: ERROR:  syntax error at or near ":"
LINE 7: ...cial_signature , qb_object , print_grouped_items, :tenant_id...

One Answer

That won't work, because the $$ that surround the body of your DO statement delimit a string literal, just like single quotes. And psql won't interpolate variables in a string literal.

You should write a PL/pgSQL function and pass the variable as argument in the function call. If you need the function only temporarily, you can use the undocumented feature of a temporary function: create the function in schema pg_temp, and it will vanish automatically when your database session ends.

Answered by Laurenz Albe on December 19, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP