Closed

Need Example for Postgres 11 - Procedures

I need help with a simple script. Need advice and a working example of how to run a Procedure with Commit and returning a dummy result back from it.

I have a example that you can reference but it is not working perfectly yet. i am not able to retrieve the result from the refcursor after i add the commit.

e.g. script

CREATE OR REPLACE PROCEDURE oureadata.pg_boweb_cms_category_update_v2(pid character, pcategoryname character varying, pwebcode character varying, porder numeric, pupdatedby character varying, pupdateddate timestamp without time zone, plang character varying, INOUT cresults refcursor)

LANGUAGE plpgsql

AS $procedure$

DECLARE

cnt bigint;

rtnCode char(1);

BEGIN

cresults := 'cur';

BEGIN

update oureadata.CMS_CATEGORY

set FCATEGORYNAME = pCategoryName,

FWEBCODE = pWebCode,

FORDER = pOrder,

FUPDATEDBY = pUpdatedBy,

FUPDATEDDATE = pUpdatedDate,

FLANGCODE = pLang

where lower(FID) = lower(pId);

commit;

end;

OPEN cresults FOR VALUES ('stringresult');

end;

$procedure$

AND to execute the above script.. i use :

BEGIN;

CALL oureadata.pg_boweb_cms_category_update_v2('e72fa40e-2447-4d59-b6cd-76625d180c32',

'1',

'SG988',

3,

'testadmin',

'2018-01-11 14:32:50',

'en-gb',

'');

fetch all in cur;

commit;

PM me if you can help.

Skills: Database Administration, Oracle, PostgreSQL, SQL

See more: need example article review, need example sign office hours, need example script, postgresql blogs, psql, postgres world, postgresql ><>, postgres 11 stored procedures, postgres news, postgresql 11, best postgresql blog, java, oracle, help need quick simple website, need example trifold brochure actual dental office, need example header business, need example brochure, need example quartly profit loss statement, usa cpa leads need example, need example plan review administration system

About the Employer:
( 5 reviews ) Tampines, Singapore

Project ID: #17261116

4 freelancers are bidding on average $29 for this job

$36 SGD in 3 days
(6 Reviews)
3.6
Darwin21015

Hello how are you, I hope you are well, I am interested in the example you need in postgreql, I have experience in these topics, I can help you in stored procedures and functions, complex and simple views, triggers, et More

$36 SGD in 1 day
(3 Reviews)
3.0
ABUNTYGOSWAMI

hey, I am from India. I am graduate from India. I have done many projects in a different language. like ionic, Oracle database, MySQL database, database administration, PHP, Java, javascript, desktop applications, web More

$26 SGD in 1 day
(0 Reviews)
0.0
mjigr

I have experence in plpgsql. I can explaine You why this procedure w doesn't work, and rewrite it to meet you requirements.

$18 SGD in 1 day
(0 Reviews)
0.0