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...
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP