Stored Procedure / Stored Function: SELECT in PostgreSQL

Question

I have created a stored procedure / function with a simple SELECT in PostgreSQL:

CREATE FUNCTION select_proc2()
RETURNS SETOF procedure AS
$DELIMETER$
SELECT * FROM procedure;
$DELIMETER$
LANGUAGE 'sql'

This one works but when I tried to be specific like this:

CREATE FUNCTION select_proc2(INT)
RETURNS SETOF procedure AS
$DELIMETER$
SELECT "Fname" FROM procedure where "Id" = $1;
$DELIMETER$
LANGUAGE 'sql'

it returns an error:

ERROR:  return type mismatch in function declared to return procedure
DETAIL:  Final statement returns character instead of integer at
column 1. CONTEXT:  SQL function "select_proc2"

I tried any solution that I can think of. Anyone here know how to solve this error?

From your table definition we would be able to see whether you actually named the column with double-quoted CamelCase ("Id", "Fname") or you need to remove the double-quotes. — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 3, 2013 at 23:18

Erwin Brandstetter · Accepted Answer · 2013-02-03 22:51:40Z

1

You need to adapt the RETURN type to what's actually returned:

CREATE OR REPLACE FUNCTION select_proc2(int)
  RETURNS SETOF text AS
$func$
SELECT "Fname" FROM procedure WHERE "Id" = $1;
$func$ LANGUAGE sql

In your second version you only return one column. From the name I am deriving the data type text, but that's just a guess.

If "Id" is the primary key column of procedure or otherwise defined UNIQUE, only one row can be returned and you can simplify to:

  RETURNS text

Also don't quote the language name. sql is an identifier here, not a string literal. It's only tolerated for historic reason, but it's probably going to be an error in future versions.

Concerning your column names: My advise is to use non-quoted lower-case identifiers exclusively in Postgres. Start by reading the chapter "Identifiers and Key Words" to learn about the significance of "id", "Id", ID or id as column name.

edited Feb 3, 2013 at 22:51

answered Feb 3, 2013 at 22:33

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

Comments

kofemann · Accepted Answer · 2013-02-02 17:57:07Z

0

remove quotes around field names (without "):

  CREATE FUNCTION select_proc2(INT)
  RETURNS SETOF procedure AS
  $DELIMETER$
  SELECT Fname FROM procedure where Id = $1;
  $DELIMETER$
  LANGUAGE 'sql'

answered Feb 2, 2013 at 17:57

kofemann

4,4731 gold badge38 silver badges43 bronze badges

5 Comments

user2035657 Over a year ago

I tried but it show an error ERROR: column "fname" does not exist LINE 4: SELECT Fname FROM procedure where Id = $1;

kofemann Over a year ago

well, then you have to provide the table description

user2035657 Over a year ago

table description?..can you give me an example of it?

Erwin Brandstetter Over a year ago

The advise may or may not help, the actual (obvious) error is not pointed out.

user330315 Over a year ago

Don't quote the language name, it should be sql, not 'sql'.

Collectives™ on Stack Overflow

Stored Procedure / Stored Function: SELECT in PostgreSQL

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related