pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCreating a boolean function
5+ messages / 5 participants
[nested] [flat]
* Creating a boolean function
@ 2001-09-19 18:55 Miguel González <iafmgc@unileon.es>
2001-09-19 19:36 ` Re: Creating a boolean function Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-09-19 19:54 ` Re: Creating a boolean function Jeff Eckermann <jeckermann@verio.net>
2001-09-19 20:19 ` Re: Creating a boolean function Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 5+ messages in thread
From: Miguel González @ 2001-09-19 18:55 UTC (permalink / raw)
To: Haller Christoph <ch@rodos.fzk.de>; +Cc: pgsql-sql
by the way you know how to create the following sql function?
I need to return a boolean saying if an object is onloan.
the loan table is as follows:
loans
-----
code_loan
code_user
typetable
i want to check these three fields, the first two fields (the code ones) are
integer and the last a string.
I tried
create function check_loan (text,int,int)
returns bool
as
'select CAST(typetable AS text) from loans where typetable=$1 and
code_user=$2 and code_loan=$3;'
language 'sql';
But I got that the parser cannot identify the =$ operator and requires me to
do the proper casting.
How can I do it? Sorry for the questions, but I am new at creating functions
and I have to hand in this project tomorrow.
Many thanks
Miguel
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a boolean function
2001-09-19 18:55 Creating a boolean function Miguel González <iafmgc@unileon.es>
@ 2001-09-19 19:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2001-09-19 19:36 UTC (permalink / raw)
To: Miguel González <iafmgc@unileon.es>; +Cc: pgsql-sql
On Wed, 19 Sep 2001, [iso-8859-1] Miguel González wrote:
> I tried
>
> create function check_loan (text,int,int)
> returns bool
> as
> 'select CAST(typetable AS text) from loans where typetable=$1 and
> code_user=$2 and code_loan=$3;'
> language 'sql';
>
>
> But I got that the parser cannot identify the =$ operator and requires me to
> do the proper casting.
>
> How can I do it? Sorry for the questions, but I am new at creating functions
> and I have to hand in this project tomorrow.
You can probably get away with just putting a space before the arguments,
so '= $1' rather than '=$1'.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a boolean function
2001-09-19 18:55 Creating a boolean function Miguel González <iafmgc@unileon.es>
@ 2001-09-19 19:54 ` Jeff Eckermann <jeckermann@verio.net>
2001-09-19 22:03 ` Re: Creating a boolean function Kovacs Baldvin <kb136@hszk.bme.hu>
2 siblings, 1 reply; 5+ messages in thread
From: Jeff Eckermann @ 2001-09-19 19:54 UTC (permalink / raw)
To: Miguel González <iafmgc@unileon.es>; +Cc: pgsql-sql
Maybe the reason is that you defined the function to return bool, but are
attempting to return text?
You need conditional logic, which suggests pl/pgsql is the way to go.
Something simple should do it, like:
create function check_loan (text,int,int)
returns bool
as '
begin
select * from loans where typetable=$1 and code_user=$2 and code_loan=$3;
if found then return true;
else return false;
end if;
return true;
end;
' language 'plpgsql';
Since this is your project, I will take the luxury of not testing that code
:-)
----- Original Message -----
From: "Miguel González" <iafmgc@unileon.es>
To: "Haller Christoph" <ch@rodos.fzk.de>
Cc: "PostgreSQL SQL" <pgsql-sql@postgresql.org>
Sent: Wednesday, September 19, 2001 1:55 PM
Subject: Creating a boolean function
> by the way you know how to create the following sql function?
>
> I need to return a boolean saying if an object is onloan.
>
> the loan table is as follows:
>
> loans
> -----
> code_loan
> code_user
> typetable
>
>
> i want to check these three fields, the first two fields (the code ones)
are
> integer and the last a string.
>
> I tried
>
> create function check_loan (text,int,int)
> returns bool
> as
> 'select CAST(typetable AS text) from loans where typetable=$1 and
> code_user=$2 and code_loan=$3;'
> language 'sql';
>
>
> But I got that the parser cannot identify the =$ operator and requires me
to
> do the proper casting.
>
> How can I do it? Sorry for the questions, but I am new at creating
functions
> and I have to hand in this project tomorrow.
>
> Many thanks
>
> Miguel
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a boolean function
2001-09-19 18:55 Creating a boolean function Miguel González <iafmgc@unileon.es>
2001-09-19 19:54 ` Re: Creating a boolean function Jeff Eckermann <jeckermann@verio.net>
@ 2001-09-19 22:03 ` Kovacs Baldvin <kb136@hszk.bme.hu>
0 siblings, 0 replies; 5+ messages in thread
From: Kovacs Baldvin @ 2001-09-19 22:03 UTC (permalink / raw)
To: Jeff Eckermann <jeckermann@verio.net>; +Cc: Miguel González <iafmgc@unileon.es>; pgsql-sql
> You need conditional logic, which suggests pl/pgsql is the way to go.
Maybe he could use simple sql function like this:
create function check_loan(text,int,int) returns boolean as '
select (
select count(*) from kolcsonok where
code_user=($2) and code_loan=($3) and typetable=($1)
) > 0;
' language 'sql';
The immediate benefit of it is that there's no need to install
a procedural languge for this simple task.
Regards,
Baldvin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Creating a boolean function
2001-09-19 18:55 Creating a boolean function Miguel González <iafmgc@unileon.es>
@ 2001-09-19 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2001-09-19 20:19 UTC (permalink / raw)
To: Miguel González <iafmgc@unileon.es>; +Cc: pgsql-sql
=?iso-8859-1?Q?Miguel_Gonz=E1lez?= <iafmgc@unileon.es> writes:
> But I got that the parser cannot identify the =$ operator
You need a space between = and $.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-09-19 22:03 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-19 18:55 Creating a boolean function Miguel González <iafmgc@unileon.es>
2001-09-19 19:36 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-09-19 19:54 ` Jeff Eckermann <jeckermann@verio.net>
2001-09-19 22:03 ` Kovacs Baldvin <kb136@hszk.bme.hu>
2001-09-19 20:19 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox