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

SampleDb.backup

Sample Database  did not attach with original posting

Thanks

Olukay

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

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.

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

Hi,

The error clearly states that the number of columns in the insert

insert into parametertable(postingaccount,sourcedebitamount,sourcecreditamount,recordid,company)

Select allOpen 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)

Select allOpen 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

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

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

 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

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

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.

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

>>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.

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

Select allOpen 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

Select allOpen in new window

Regards,
     Tomas Helgi

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.

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

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

Hi,

Here is an dbfiddle example.

Regards,
    Tomas Helgi

But how do you incorporate returning the count inthe same statement as the insert to return the number of rows inserted?

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

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.

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

Thanks a lot for getting me over the finishing line.

I could not ask for more