Avatar of Olukayode Oluwole
Olukayode OluwoleFlag for Canada asked on

How can i get my Stored Procedure to drop and Create a parameter Table

This problem is a follow up to ny earlier post

"How do i provide a column definition list in my stored procedure"

The slight difference now from my earlier post is that I want the script to

1. Drop a physical table parametertable

2.  Based on 2 parameters supplied (recordidx and companycodex) i want the parameter re written based on the UNION  views

3. I attach a sample  postgresqlsql database contain 3 tables (appurchase, appurchasedetail,, syscompany and parametertable)  to
test the script.

My current working script is shown below

CREATE OR REPLACE FUNCTION public.spqrypurchaseorder_postrecordy(
    IN companycodex text,
    IN recordidx text)
  RETURNS TABLE(postingaccount text, sourcedebitamount numeric, sourcecreditamount numeric, recordid text, company text) AS
$BODY$
BEGIN
delete from parametertable z where z.company = companycodex and z.recordid = recordidx;
insert into parametertable(postingaccount,sourcedebitamount,sourcecreditamount,recordid,company)
select (b.arsaledinventoryaccount,sum(b.appoditemtotal) ,0.00,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
group by b.arsaledinventoryaccount,recordidx,companycodex
UNION
select (c.syscompurchasemiscacct,a.appomisccharges ,0.00,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appomisccharges > 0
UNION
select (c.syscompurchasefreightacct,a.apposhipping ,0.00,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.apposhipping > 0
UNION
select (c.syscompurchasediscountacct,0.00,a.appodiscountamt ,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appodiscountamt > 0
UNION
select (c.syscompurchaseapacct,0.00,a.appototalamount ,recordidx,companycodex)
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appototalamount > 0;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.spqrypurchaseorder_postrecordy(text, text)
  OWNER TO postgres;

I  am also supplying 2 parameters  (NNPC  and PO-1070520)  to test the script


The current error which i have with the script is shown below:

Error screen while executing the script
I will grateful for your help

Olukay
DatabasesPostgreSQLSQL

Avatar of undefined
Last Comment
Olukayode Oluwole

8/22/2022 - Mon
ASKER
Olukayode Oluwole

SampleDb.backup

Sample Database  did not attach with original posting

Thanks

Olukay
Máté Farkas

Sorry, is that az Oracle database or which platform do you use?
ste5an

1. Drop a physical table parametertable
This is a bad idea. Cause for many SQL systems this means that you'll force a new query plan compilation.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Olukayode Oluwole

this is postgresql

Below is  the create script  for the parameter table

[]CREATE TABLE public.parametertable
(
  postingaccount character varying(10),
  sourcedebitamount numeric(18,2),
  sourcecreditamount numeric(18,2),
  recordid character varying(25),
  company character varying(25),
  id integer NOT NULL DEFAULT nextval('parametertable_seq'::regclass),
  CONSTRAINT pk_parametertable PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.parametertable
  OWNER TO postgres;[/code]


Thanks

Olukay
Tomas Helgi Johannsson

Hi,

The error clearly states that the number of columns in the insert
insert into parametertable(postingaccount,sourcedebitamount,sourcecreditamount,recordid,company)

Open in new window

which is 6 columns does not match the number of columns in the select statements (all select statements in the union have 5 columns)
select (c.syscompurchasemiscacct,a.appomisccharges ,0.00,recordidx,companycodex)

Open in new window

Thus either the select statment is missing a column or the insert statement has a column that is not suppose to be there.

Regards,
    Tomas Helgi

James0628

The error clearly states that the number of columns in the insert ... which is 6 columns ...

 I count 5 columns in the Insert (but I'm not familiar with PostgreSQL, so I may be missing something).

 I agree about what the error message seems to be saying, but I'm not seeing any discrepancy.

 James
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tomas Helgi Johannsson

Hi,

Yes, it seems I made a calculation mistake. (Doh! ).
However the error points to line number 4 which is not clear in the question.
The error clearly states that the number of columns in the insert is larger than in the select used in the insert.

Regards,
   Tomas Helgi
James0628

 I'm not sure if the OP actually posted the query that gets the error, since they described the posted query as "My current working script" (emphasis mine).

 James
ASKER
Olukayode Oluwole

I have resolved the No of columns issues

Note:  I have now have 6 columns.

I tried the 5 select statement they all worked well  selecting the records i expected

However when i tried to make it a stored procedure I ran into any error.

The revised script is as below  and i will explain the problem i am having

CREATE OR REPLACE FUNCTION public.spqrypurchaseorder_postrecordy(
    IN companycodex text,
    IN usernamex text,
    IN recordidx text)
  RETURNS TABLE(postingaccount text,sourcedebitamount numeric,sourcecreditamount numeric,recordid text,loginusername text, companycode text) AS
$BODY$
delete from parametertable where recordid = recordidx and loginusername = usernamex  and companycode = companycodex;
insert into parametertable(postingaccount,sourcedebitamount,sourcecreditamount,recordid,loginusername,companycode)
select distinct b.arsaledinventoryaccount,sum(b.appoditemtotal) ,0.00,recordidx,usernamex,companycodex
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   b.appoditemtotal > 0
group by b.arsaledinventoryaccount
UNION
select distinct c.syscompurchasediscountacct,0.00,a.appodiscountamt ,recordidx,usernamex,companycodex
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appodiscountamt > 0
UNION
select distinct c.syscompurchasemiscacct,a.appomisccharges ,0.00,recordidx,usernamex,companycodex
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appomisccharges > 0
UNION
select distinct c.syscompurchasefreightacct,a.apposhipping ,0.00,recordidx,usernamex,companycodex
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.apposhipping > 0
UNION
select distinct c.syscompurchaseapacct,0.00,a.appototalamount ,recordidx,usernamex,companycodex
from  appurchase a , appurchasedetail b, syscompany c
where a.appoextdocumentno = recordidx
and   a.appodocumentno = b. appoddocumentno
and   a.companycode = companycodex
and   b.companycode = companycodex
and   c.companycode = companycodex
and   a.appototalamount > 0
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.spqrypurchaseorder_postrecordy(text, text, text)
  OWNER TO postgres;


When I tried to create the stored procedure with the exact script above i got an error
See Error screen below:



I then tried all i could and found out that if i comment out the Insert line  the stored procedure gets created ( see  screen Below)

How do i specify the insert line  or should the table name be in the section that is returning a TABLE because that section simply states TABLE without a Table name

If anybody has a small script that is  returning a TABLE  as mine and running against postgresql database that will be very useful

Grateful for further advise


Olukay



All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
slightwv (䄆 Netminder)

The error messages tell you what the issue is.

A function that returns a table needs to have rows to return.  An insert statement doesn't return rows.  The select does which is why it works when you comment out the insert.

What do you want the function to return after you insert the rows?  a True/False, the number or rows inserted, what?  It cannot return rows unless you select something.
ASKER
Olukayode Oluwole

Once the records get written to the database parameter table  I am happy to have either no of rows or true or false reported. provided the return value will not be part of the parametertable

How do i specify it , to report no of rows

Thanks
slightwv (䄆 Netminder)

>>How do i specify it , to report no of rows

Change the function to return an int or bigint depending on the maximum number of rows you will be inserting.

Then check out the docs:  43.5.5. Obtaining the Result Status
https://www.postgresql.org/docs/11/plpgsql-statements.html

There are examples all over the Web.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tomas Helgi Johannsson

Hi,

You could take the select statement that you use in the insert statement and before you issue that insert then do a simple count into a variable which you return as integer,
Something like
delete from your_table where....
select count(*) into myvar from (
select .... union select ...
)
insert into your_table (
select .... union select ... 
)
return myvar

Open in new window

or
delete from your_table where....
with myresult as(
insert into your_table as(
select .... union select ... 
) returning * 
)
select count(*) into myvar from myresult
return myvar

Open in new window




Regards,
     Tomas Helgi
slightwv (䄆 Netminder)

a select count before an inert may noy be accurate if other processes can inert rows and bepending on the isolation level.  It also performs more work than necessary.
Tomas Helgi Johannsson

Hi,

a select count before an inert may noy be accurate if other processes can inert rows and bepending on the isolation level.  It also performs more work than necessary.                                  

Thats why I proposed to use the insert .... returning * method as shown above. ;)
Such statements inserts and returns the inserted data to a variables or select statements to be used further. All this in 1 callable statement instead of multiple calls to the database.

Regards,
    Tomas Helgi
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

I'm not familiar with using  using a select count(*) and in insert returning.  I also can't seem to get that syntax to work for Postgres on dbfiddle.uk.  Can you post a quick example?  https://dbfiddle.uk/?rdbms=postgres_11
Tomas Helgi Johannsson

Hi,

Here is an dbfiddle example.

Regards,
    Tomas Helgi
slightwv (䄆 Netminder)

But how do you incorporate returning the count inthe same statement as the insert to return the number of rows inserted?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Tomas Helgi Johannsson

Hi,

You select the count(*) into a variable declared in the function and return that as a result from the function.
That is if you don't want to return the result as a TABLE from the function as can be done using this method as well.

Regards,
    Tomas Helgi
slightwv (䄆 Netminder)

I understand the variable.  What I'm not understaning is how you get the count from the insert in a single statement.  And if it can be done, how to not do all the extra work of querying the data back out.

My guess is GET DIAGNOSTICS would be much more efficient.
ASKER CERTIFIED SOLUTION
Tomas Helgi Johannsson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Olukayode Oluwole

Hi  Tomas,

I think this method will be more efficient and i will try it now.
Can you provide the full statement that will enable
the  store procedure get saved  into the  database.
This code snippet  you have sent  starts  with "with rows"  Is this syntax true for postgresql

That is where I am having my problem ( The Top portion  before $BODY$   and the lower after $BODY$)

If you have a sample code that  works and  is complete that will be useful

Thanks

Olukay



Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Olukayode Oluwole

Thanks a lot for getting me over the finishing line.

I could not ask for more