Re: [SQL] Functions return a select in a table, which data type I
Ð ÐÑÐ, 22.10.2004, Ð 15:38, Markus Bertheau ÐÐÑÐÑ: > CREATE TYPE foo_type AS (cod_aluno TEXT, nome TEXT, cpf TEXT); > CREATE FUNCTION bar(int4) > RETURNS SETOF foo_type > LANGUAGE 'SQL' That should be LANGUAGE 'plpgsql' > AS ' > DECLARE > var_rec foo_type; > BEGIN > FOR var_rec IN SELECT cod_aluno, nome, cpf FROM table WHERE ... LOOP > RETURN NEXT var_rec; > END LOOP; > RETURN; > END; > '; And if you want that function in SQL, there are two kinds of situations, for which the solutions differ: If the record structure that the function should return is the same as the structure of a table, you can use the table name as the type. If this is not the case, you have to create a custom type: CREATE FUNCTION bar(int4) RETURNS table_name or custom_type_name LANGUAGE 'SQL' AS ' SELECT whatever FROM table WHERE field = $1 AND foo; END; '; $1 is the value of the first argument. -- Markus Bertheau <[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] How do you compare (NULL) and (non-NULL)?
Bruno Wolff III wrote: On Tue, Oct 26, 2004 at 16:23:20 -0400, Wei Weng <[EMAIL PROTECTED]> wrote: In the following query SELECT Parent FROM Channels ORDER BY Parent ASC; If I have a couple of (NULL)s in the field [Parent], they will be listed at the bottom of the query result. Is it because PostgreSQL considers (NULL) as the biggest value? If I run the same query under MSSQL Server 2000, I get the exact opposite result regarding the order of (NULL)s and (non-NULL) values. They are listed at the very beginning of the query result. If the order matters, you can order by IS NULL or IS NOT NULL. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match How do I write that? Thanks Wei ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] extra info - curious delay on view/where
Hi, Andre, On Thu, 28 Oct 2004 11:53:25 -0300 "andre.toscano" <[EMAIL PROTECTED]> wrote: > Could an INDEX speed up that SELECT? > > > CREATE VIEW "stock_available" as > > > SELECT * FROM stock_details > > > WHERE available = true AND visible = true AND > > > location not in (SELECT descr FROM ignored); Yes, I'm shure. I would try to create (on the underlying table) a conditional index on the column "location" with the condition "available = true AND visible = true". As often, the acutal effect depends on the count of rows the query returns compared to the total rows in the table. And it would be helpful to know the typical queries (especially the rows in the WHERE clauses) to give additional hints on creating indices. A matching index also potentially speeds up ORDER BY queries. HTH, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How do you compare (NULL) and (non-NULL)?
On Fri, Oct 29, 2004 at 11:59:15 -0400, Wei Weng <[EMAIL PROTECTED]> wrote: > > How do I write that? SELECT Parent FROM Channels ORDER BY Parent IS NULL, Parent ASC; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
