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?
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;
