Received: from relay.mail.insnet.cw.net (relay.mail.insnet.cw.net [213.38.238.97]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7ECgJP96808 for ; Tue, 14 Aug 2001 08:42:19 -0400 (EDT) (envelope-from gary.stainburn@ringways.co.uk) Received: from stan.ringways.co.uk ([213.38.87.130]) by relay.mail.insnet.cw.net (8.11.2/8.11.2) with ESMTP id f7ECgFB18122 for ; Tue, 14 Aug 2001 13:42:15 +0100 (BST) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with smtp (Exim 3.13 #1) id 15WdIX-0001XS-00 for pgsql-sql@postgresql.org; Tue, 14 Aug 2001 13:27:53 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: pgsql-sql@postgresql.org Subject: create function using language SQL Date: Tue, 14 Aug 2001 13:40:05 +0100 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01081413400502.01217@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200108/178 X-Sequence-Number: 4400 Hi all, As I was reading up on create function etc. while learning plpgsql, I seam to remember it stated that as well as plpgsql, that other languages are/will be available including using SQL as the language. However, I cannot find the syntax to create a function in SQL. Specifically, how you return the result. As an example, how would I create a SQL function to match the plpgsql function below? CREATE FUNCTION getteamno(int4) RETURNS varchar AS ' DECLARE unitno varchar; BEGIN select into unitno tregion || ''/'' || to_char(tnumber,''FM000'') from teams where tid = $1; if not found then raise exception ''Team % not found'',$1; return ''''; end if; return unitno; END; ' LANGUAGE 'plpgsql'; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000