CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Pooja
Pooja

Reputation: 333

Issue While creating function in PostgreSQL "ERROR: syntax error at or near "ROWTYPE""

Below is a simple procedure in PL/SQL

PROCEDURE emp_get_rec (emp_rec IN OUT NOCOPY emp_content%ROWTYPE)
  IS
    v_cnt   NUMBER;
  BEGIN
    SELECT COUNT(*)
    INTO v_cnt
    FROM emp_content
    WHERE emp_id = emp_rec.emp_id;
    IF v_cnt = 1
    THEN
      SELECT * INTO emp_rec
      FROM emp_content
      WHERE emp_id = emp_rec.emp_id;
    END IF;
 END emp_get_rec;

Which I am trying to convert in PostgreSQL,

Create or replace function emp_get_rec (emp_rec IN OUT emp_content%ROWTYPE)
AS $BODY$
DECLARE
    v_cnt   NUMBER;
BEGIN

       SELECT COUNT(*)
    INTO v_cnt
    FROM emp_content
    WHERE emp_id = emp_rec.emp_id;
    IF v_cnt = 1
    THEN
      SELECT * INTO emp_rec
      FROM emp_content
      WHERE emp_id = emp_rec.emp_id;
    END IF;
 END;
$BODY$ LANGUAGE 'plpgsql';

I am facing following error:

ERROR: syntax error at or near "ROWTYPE"

Upvotes: 4

Views: 1980

Answers (1)

Abelisto
Abelisto

Reputation: 15624

Documentation says:

Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type.

So you are able to use the table name as the type name where syntactically it means a type:

create or replace function emp_get_rec (emp_rec IN OUT emp_content)

Upvotes: 1

Related Questions