How can I fix this simple script to run correctly in Postgres 16?

Question

I'm having troubles getting the following simple script to run in VS Code (using the MySQL plugin) on Postgres 16rc1.

DO $$ BEGIN
-- Declare a variable of user-defined type utTable.
DECLARE data1 mySchema.utTable;
-- Prefill the variable for use in a function call
INSERT INTO data1(utCol1, utCol2, utCol3)
    VALUES
    ('a0', 1, 'test value 0'),
    ('a1', 1, 'test value 1'),
    ('a2', 1, 'test value 2a'),
    ('a2', 2, 'test value 2b'),
    ('a3', 1, 'test value 3');
-- Call a function and pass the prefilled variable to it
SELECT * FROM CALL mySchema.fnDoStuff('blah', data1);
END $$;

When I execute the script, I get the following error message syntax error at or near "INTO"

I have tried with just a BEGIN block, I also tried changing the INSERT INTO for a SELECT ... INTO, but nothing I've tried worked.

Update:

Yes, I'm in the process of trying to convert my project from SQL Server to Postgres, and unlearning stuff is not always easy.

Regarding the comments stating that there are no table variables, that might be the case, however both Functions and Procedures can accept table parameters - so, what is the "best practice" approach to filling a variable (table) that I can pass to a function or procedure within a small script?

Further more, regarding the comments that DO can't return anything - I was only trying to use the DO because within the VS Code editor, when I don't use the DO all my statements get separated into individual statements, which then causes a problem for the INSERT as the varaible data1 is then unknown.

The sole purpose of the script is to test the function that I created. The function is intended to be called from a Java application, however before I start on the Java code, I wanted to be able to test and confirm that the function works as intended, and for that I need to be able to pass a small set of test data to it - the table.

Why using 16rc1 when 16.0 is available? Besides that, a DO statement can't return anything so your SELECT will result in an error. Not sure what you try to achieve, but it looks like you could this with just a single query without the DO statement. — Frank Heikens
– Frank Heikens, Commented Oct 17, 2023 at 18:22
I was hoping that the comments in the code would explain what I am trying to do. The SELECT at the end is only there for my own debugging - a variation of the code was originally writte for T-SQL. Regarding your single query, how would that go? — Eric
– Eric, Commented Oct 17, 2023 at 18:36
What would work is reading the docs Declarations and Executing a Command. — Adrian Klaver
– Adrian Klaver, Commented Oct 17, 2023 at 19:44
You are trying to shoehorn T-SQL code into a Postgres DO command. Syntax and the whole approach are not right. For starters, there are no table variables in PL/pgSQL. See: stackoverflow.com/a/10790059/939860, stackoverflow.com/a/34034929/939860 Next, you cannot return from a DO command. stackoverflow.com/a/14653151/939860 You have to rethink your approach. — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 17, 2023 at 19:50
Hello @ErwinBrandstetter, I wouldn't call it shoehorn but yes, the original test-script was written for T-SQL. The scripts only purpose is to test the function, for which I need to be able to pass test data to it and eventually see the results from the function - any help in being able to do that, would be greatly appreciated. — Eric
– Eric, Commented Oct 19, 2023 at 7:41

Eric · Accepted Answer · 2023-10-22 07:42:53Z

After reading the suggested documentation and then a lot more googling, I now know what I did wrong. For that reason, I'm now adding this answer, so that it might help someone else in the future.

The main issue was that, I had thought, by creating a user defined type (record) that it represented a table - it doesn't! The user defined type only represents a record (or single table row if you like). This meant, the definition of the function also only accepted a single table row (record). So, I had to change the function definition as follows:

CREATE OR REPLACE FUNCTION mySchema.fnDoStuff(par1 VARCHAR(50), par2 mySchema.utTable[]) -- An array of records.

Reminder: The user defined type mySchema.utTable represents a record, not a table as the misleading name might suggest.

After changing the function so that it accepted multiple records, I then had to change how I called the function:

SELECT mySchema.fnDoStuff
    (
        'blah',  -- parameter 1 VARCHAR
        ARRAY[   -- parameter 2 ARRAY of type mySchema.utTable
            ROW('z0', 1, 'test 0'),
            ROW('z1', 1, 'test 1'),
            ROW('z2', 1, 'test 2a'),
            ROW('z2', 2, 'test 2b'),
            ROW('z3', 1, 'test 3')
        ]::mySchema.utTable[]);

With this single call, I now, finally, have a way to test the function I have created and pass an array of records (table) to the function for processing.

Collectives™ on Stack Overflow

How can I fix this simple script to run correctly in Postgres 16?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related