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 $$
   TYPE employee_collection IS TABLE OF employee%ROWTYPE;
   emp_data employee_collection;
   SELECT * 
   FROM employee;

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.

  • 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.
    – mustaccio
    Feb 8 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.
    – Vérace
    Feb 28 at 10:56

1 Answer 1


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.

  • 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.
    – James
    Feb 9 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."
    – Phill W.
    Feb 13 at 15:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.