Received: from akirason.se.verio.net (akirason.se.verio.net [204.27.64.122]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7EDscP19346 for ; Tue, 14 Aug 2001 09:54:38 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: from beancounter (no-wkstns.gs.verio.net [204.27.67.225]) (authenticated) by akirason.se.verio.net (8.11.4/8.11.4) with ESMTP id f7EDpXc17483; Tue, 14 Aug 2001 08:51:34 -0500 (CDT) (envelope-from jeckermann@verio.net) Message-ID: <007c01c124c8$a3ae41d0$039c10ac@verio.net> From: "Jeff Eckermann" To: "Gary Stainburn" Cc: References: <01081413400502.01217@gary.ringways.co.uk> Subject: Re: create function using language SQL Date: Tue, 14 Aug 2001 08:40:41 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 X-Archive-Number: 200108/185 X-Sequence-Number: 4410 The "create function" syntax is the same. The language syntax is straight SQL: CREATE FUNCTION getteamno(int4) RETURNS varchar AS ' SELECT tregion || ''/'' || to_char(tnumber, ''FM000'') FROM teams WHERE tid = $1 ' LANGUAGE 'sql'; You don't get to build in error checking, but there has to be a reason to use plpgsql :-) SQL is non-procedural, after all. Check the docs: Reference Manual/SQL Commands/Create Function ----- Original Message ----- From: "Gary Stainburn" To: Sent: Tuesday, August 14, 2001 7:40 AM Subject: create function using language SQL > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > >