How to extract data from Blob in pgsql DB with query
Hi All,
I need to extract how many users do not have ext authentication enabled in operators tables. pyImportedUser is false it means no external authentical.
I wrote a query like below it's not working please help me.
Select pr_read_from_stream('pyImportedUser',pzInsKey,pzpvstream) from DATA.PR_OPERATORS;
SELECT data.pr_read_int_from_stream('pyImportedUser',(SELECT pzpvstream FROM DATA.PR_OPERATORS ) );
***Edited by Moderator Marissa to add SR Details***
IN
Hi Pavani,
pr_read_int_from_stream function takes 3 parameters .
parameter 1: Property
parameter 2: pzinskey
parameter 3: Blob
Please try the below example query :
SELECT <database>.<schema>.pr_read_from_stream('pyImportedUser','DATA-ADMIN-OPERATOR-ID Proprietary information hidden',(SELECT pzpvstream FROM <database>.<schema>.PR_OPERATORS where pzInsKey='DATA-ADMIN-OPERATOR-ID Proprietary information hidden') );
IN
Hi Pural,
Thanks for your response ,i executed as below in PGSQL
SELECT data.pr_read_from_stream('pyImportedUser','DATA-ADMIN-OPERATOR-ID Proprietary information hidden',(SELECT pzpvstream FROM data.PR_OPERATORS where pzInsKey='DATA-ADMIN-OPERATOR-ID Proprietary information hidden') );
I got error as No function matches the given name and argument types. You might need to add explicit type casts.
I didnt give database name in data.pr_read_from_stream, i gave only schema name .
IN
Hi Pavani,
The below query gets executed successfully in MSSQL , can you please frame similar query as per POSTGRES syntax and check ?
SELECT <database>.<schema>.pr_read_from_stream('pyImportedUser','DATA-ADMIN-OPERATOR-ID Proprietary information hidden',(SELECT pzpvstream FROM <database>.<schema>.PR_OPERATORS where pzInsKey='DATA-ADMIN-OPERATOR-ID Proprietary information hidden') );
IN
Hi Pural,
SELECT bpms01d.data.pr_read_from_stream('pyImportedUser','DATA-ADMIN-OPERATOR-ID Proprietary information hidden',(SELECT pzpvstream FROM data.PR_OPERATORS where pzInsKey='DATA-ADMIN-OPERATOR-ID Proprietary information hidden') );
I tried this it didnt work.
Pegasystems Inc.
US
This is the query that you want, assuming that data is the name of your data schema.
Select data.pr_read_from_stream('pyImportedUser',pzInsKey,pzpvstream) from data.PR_OPERATORS