PostgreSQL function, need to get variables inside the function

Question

I need some help with defining a Function in postgreSQL:

here are my table definitions:

CREATE TABLE venue (
   id INTEGER DEFAULT NEXTVAL('venue_id_seq')
 , building_code building_code
 , floorNo int
 , roomNo int
 , width int
 , length int
);

I need to create a function related to that table that gives me the Floor area.Here is what I have so far:

CREATE FUNCTION floorArea(id int) RETURNS int AS '
SELECT  venue.width * venue.length AS result ;
' LANGUAGE SQL;

I don't know how to possibly make variables etc.
I want something along the lines of:

var width=  Select width from venue where id=$1;
var length=  Select length from venue where id=$1;
return width * length;

Community · Accepted Answer · 2017-05-23 11:49:51Z

2

There are no variables inside an SQL function. You need to use PL/pgSQL or another procedural language for that.

With plpgsql it could look like this:

CREATE FUNCTION floor_area(_id int)
  RETURNS int AS
$func$
DECLARE
   _width   int;
   _length  int;
BEGIN

SELECT INTO _width, _length
             width,  length
FROM   venue
WHERE  id = _id;

RETURN width * length;

END
$func$ LANGUAGE plpgsql;

Consider what I wrote about parameter names in my other answer.

But a simple task like this should be solved with plain SQL without variables like @Roman already supplied.

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Aug 14, 2013 at 16:03

Erwin Brandstetter

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

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

Comments

roman · Accepted Answer · 2013-08-14 14:46:07Z

1

I think you can do something like

CREATE FUNCTION floorArea(idp int)
RETURNS int
as
$$
    select width * length from venue as v where v.id = idp
$$ LANGUAGE SQL;

sql fiddle demo

edited Aug 14, 2013 at 14:46

answered Aug 14, 2013 at 14:17

roman

118k30 gold badges205 silver badges209 bronze badges

2 Comments

Erwin Brandstetter Over a year ago

I make it a habit to prefix parameter names with _, like _id, to avoid conflicts altogether. It also helps to quickly identify parameter names in the code. Also, I also advise against unquoted CamelCase identifiers like floorArea. It leads to confusion easily. There have been several desperate questions here, stemming from that.

roman Over a year ago

I'm working with SQL Server at my job, PostgreSQL only fo fun (although I'd like to work with PostgreSQL), so we always have @ before parameters. Anyway, thanks for tip

Collectives™ on Stack Overflow

PostgreSQL function, need to get variables inside the function

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related