0

I've been learning some PL/pgSQL and am having a hard time declaring and using a table type.

I know this script is dumb and doesn't accomplish anything, I'm just trying to learn.

Here is my query:

do $$ DECLARE TYPE employee_collection IS TABLE OF employee%ROWTYPE; emp_data employee_collection; BEGIN SELECT * BULK COLLECT INTO emp_data FROM employee; END; $$

When I run this I get this error: syntax error at or near "TABLE".

Everywhere I look online this looks like the way to do this. I even copied most of this code from a website.

Is there a syntax error or is something else going on?

I'm running PostgreSQL 12.11.

Improve this question
2
  • 3
    If you're using Postgres, PL/SQL (an Oracle thing) is not something you want to (or can) use. Read the Postgres documentation for the correct Postgres syntax. Commented Feb 8, 2024 at 22:52
  • I even copied most of this code from a website. - what website - you say "most" - please show us all of this code and the bits you took - if it was an Oracle PL/SQL site, it's hardly surprising that it won't work exactly as is, even though PL/pgSQL is more or less an effort at cloning Oracle's procedural language. Commented Feb 28, 2024 at 10:56

1 Answer 1

Reset to default
0

PostgreSQL can use PL/pgSQL, PL/Tcl, PL/Perl and PL/Python.

PL/SQL is the scripting language used by Oracle Database.
Despite their naming, the two are completely incompatible.

Improve this answer
2
  • Thanks for the answer. I don't understand why it was downvoted. I added LANGUAGE plpgsql to the end and the error is the same. Commented Feb 9, 2024 at 21:27
  • Yes, you will get the same error because the code you posted is ORACLE database PL/SQL and NOT PostgreSQL plpgsql. Different DBMS, different scripting languages. The /only/ way that code will work is if you run it in an ORACLE database. There is no way to make ORACLE PL/SQL work inside PostgreSQL. Deliberately misquoted movie time: "You must write in pgplsql. Don't write in PL/SQL and transpose." Commented Feb 13, 2024 at 15:09

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.