Postgresql -CREATE FUNCTION

Question

CREATE OR REPLACE FUNCTION  udf_get_emp_name(p_empcode integer)
returns text
AS 
$BODY$

    DECLARE l_emp_name  TEXT;
   select emp_name into l_emp_name from employee  where empcode = p_empcode;

   return l_emp_name;
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

This function gets created successfully and postgresql doesn't check if table exist or column exist. Is there any option to check if column name and table names are correct and exist at the time of CREATE or REPLACE FUNCTION

user330315 · Accepted Answer · 2016-07-07 06:47:06Z

4

You don't need PL/pgSQL for this. If you make that a plain sql function, Postgres will check the presence of the table and the syntax of the embedded SQL statements:

CREATE OR REPLACE FUNCTION  udf_get_emp_name(p_empcode integer)
returns text
AS 
$BODY$
   select emp_name 
   from employee 
   where empcode = p_empcode;
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

If however you simplified your example and you do really need PL/pgSQL, then you can use the extension PL/pgSQL Check (or PL/pgSQL Lint) to verify the SQL code in PL/pgSQL functions.

answered Jul 7, 2016 at 6:47

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Sayeekrishnan Subramaniam Over a year ago

Thanks for the reply. But if i use sql, then i am unable to return query option .

user330315 Over a year ago

@SayeekrishnanSubramaniam: A SQL function can return the result of a query (as shown in the example in my answer)

Collectives™ on Stack Overflow

Postgresql -CREATE FUNCTION

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related