[SQL] calling user defined function with parameters..
Hi, I've created a function like this : CREATE FUNCTION tester(INT4) RETURNS BOOL AS ' DECLARE r RECORD; p ALIAS FOR $1; BEGIN SELECT INTO r id_dpt FROM dpts WHERE id_dpt=p; IF NOT FOUND THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END; ' LANGUAGE 'plpgsql'; All is ok at creation. But now , how can i use my function ? i would like a thing like : SELECT tester(14) AS ok; But is make a Postgresql parser error How to do then ?? Thanks for any help or links (other than the postgresql.org website...) ! ;) Regards, Fred ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] not null - trivial, unexpected behavior
John Scott <[EMAIL PROTECTED]> writes: > select count(*) from A where b = null; /* Returns 1, ok */ > select count(*) from A where b != null; /* Returns 0 ... not ok! */ Uh ... there have been several threads about this just in the past couple days. See for example http://www.ca.postgresql.org/mhonarc/pgsql-sql/2001-06/msg00102.html and followups. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] calling user defined function with parameters..
What version are you using and what error are you getting? Your example works for me on 7.2devel once i made a dpts table with an id_dpt column. The function code is not checked for plpgsql until its first use, so if there was a syntax error, it wouldn't be seen until you tried to use it. On Sat, 16 Jun 2001, Postgresql wrote: > Hi, > > I've created a function like this : > > CREATE FUNCTION tester(INT4) > RETURNS BOOL AS > ' > DECLARE > r RECORD; > p ALIAS FOR $1; > > BEGIN > SELECT INTO r > id_dpt > FROM dpts > WHERE id_dpt=p; > > IF NOT FOUND THEN > RETURN FALSE; >ELSE > > RETURN TRUE; > END IF; > END; > ' > LANGUAGE 'plpgsql'; > > All is ok at creation. > But now , how can i use my function ? i would like a thing like : > > SELECT tester(14) AS ok; > > But is make a Postgresql parser error > > How to do then ?? > > Thanks for any help or links (other than the postgresql.org website...) ! > > ;) > > Regards, > > Fred > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] casts and conversions
Craig Longman <[EMAIL PROTECTED]> writes: > select (unitcost*probability) from oppproducttype > ERROR: Unable to identify an operator '*' for types 'numeric' and > 'float8' > You will have to retype this query using an explicit cast > is this kind of thing a regular thing for postgresql? The problem here is that we use a very generic, datatype-independent algorithm for resolving operator type ambiguities. It's nice and extensible, which is great for user-defined datatypes ... but there's really no way to handle all the standard numeric datatypes in an intuitive fashion without introducing type-specific knowledge. We've had discussions about fixing this in the past (see e.g. pghackers archives from last May & June), but we've not yet come up with a solution that satisfies everyone. It's still on the to-do list though. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] not null - trivial, unexpected behavior
Peter Eisentraut <[EMAIL PROTECTED]> writes: > John Scott writes: >> but, as i understand the sql92 standard, both att = null and att != null >> are NOT sql92. > They are. We just don't implement att = null right because of reasons > that can be found in the archives. In a very narrow sense, they're not SQL92, because SQL92 doesn't actually allow an unadorned keyword NULL to appear in arbitrary expression contexts. You could legally write the expression as att = CAST (NULL AS type-of-att) and then the required result would always be NULL, a/k/a UNKNOWN (nb. this is NOT the same as FALSE). And indeed that's what Postgres will produce if you do it that way. In practice, since Postgres extends the spec to allow the unadorned keyword NULL to appear in arbitrary expressions (with implicit resolution of the datatype of the null), you'd expect that att = NULL would behave the same as if the NULL came from a CAST, evaluation of a data value, etc. But it doesn't, for reasons that have been discussed already. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Re: [HACKERS] Postgres
Guru Prasad <[EMAIL PROTECTED]> writes: > using postgres 7.1 version. Now the database got corrupted. I had no clue > how it got corrupted. Basically, i did found that the data of various > users existing (in /usr/local/pgsql/data directory). But there were no > data existing in the following system catalogs. > pg_database. (except template0 & template1) > pg_shadow. (except postgres user. Previously 15 users were there.) > pg_tables. (Not showing any user created tables. Only system tables > get displayed). > But the data structure of individual tables exist somewhere. Suppose if i > do 'select * from tablename' then field names gets displayed but says 'no > rows'. At the same time, i am unable to get the structure through > \d tablename. If there's no entry for your database in pg_database, how were you able to connect to do a 'select * from tablename'? I'd like to see exactly what you did and exactly what results you got, not your interpretations about whether there's data in tables or not. Whatever's going on here is probably more subtle than that. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
