CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsql
user3525834
user3525834

Reputation: 1

How to modify column of table via create function

We assume that there exists a table terms(id,year,sess). sess is the semester S1 or S2.

create function Q3(id1 IN integer) returns TextString 
declare tm TextString
begin
      SELECT 'year%100'||lower(sess) into tm
      FROM Terms 
      WHERE id1 = id
      return tm
END;

I got an error near declare, what's wrong with my code?

Upvotes: 0

Views: 64

Answers (1)

Pavel Stehule
Pavel Stehule

Reputation: 45795

It is almost all wrong. PostgreSQL stored procedures are written as string - you miss it, and you miss a language specification. Probably you want:

CREATE OR REPLACE FUNCTION Q3(_id integer)
RETURNS text AS $$
DECLARE _tm text;
BEGIN
  SELECT t.year % 100 || lower(t.sess) INTO _tm
     FROM terms t
    WHERE _id = t.id;
  RETURN _tm;
END;
$$ LANGUAGE plpgsql;

This code can be truncated little bit or you can use SQL language for trivial one line functions:

CREATE OR REPLACE FUNCTION Q3(id integer)
RETURNS text AS $$
SELECT t.year % 100 || lower(t.sess)
   FROM terms t
  WHERE t.id = $1;
$$ LANGUAGE sql;

see http://postgres.cz/wiki/PL/pgSQL_%28en%29

Upvotes: 1

Related Questions