We help IT Professionals succeed at work.

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

81 Views
Last Modified: 2020-05-11
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
Comment
Watch Question

Olukayode OluwoleSystems Analyst

Author

Commented:
SampleDb.backup

Sample Database  did not attach with original posting

Thanks

Olukay
Máté FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
Sorry, is that az Oracle database or which platform do you use?
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
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.
Olukayode OluwoleSystems Analyst

Author

Commented:
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 JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
Hi,

The error clearly states that the number of columns in the insert
insert into parametertable(postingaccount,sourcedebitamount,sourcecreditamount,recordid,company)
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)
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

CERTIFIED EXPERT

Commented:
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
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
 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
Olukayode OluwoleSystems Analyst

Author

Commented:
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



CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
Olukayode OluwoleSystems Analyst

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
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
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



Regards,
     Tomas Helgi
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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 JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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 JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
Hi,

Here is an dbfiddle example.

Regards,
    Tomas Helgi
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
But how do you incorporate returning the count inthe same statement as the insert to return the number of rows inserted?
Tomas Helgi JohannssonDatabase Administrator / Software Engineer
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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.
Database Administrator / Software Engineer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Olukayode OluwoleSystems Analyst

Author

Commented:
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



Olukayode OluwoleSystems Analyst

Author

Commented:
Thanks a lot for getting me over the finishing line.

I could not ask for more
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.