Hi everyone!
I recently noticed obscure behavior of ORDER BY. Consider this example:
select email from
(
select '@'::text as email
union all
select '.'::text as email
) a
order by email;
The result is:
email
---
.
@
(2 rows)
This is all normal - I expect, that dot is smaller than ampers
>
> Hi.
> What is the better way to store the last record for a translation???
> I.E:
> The data for the last product vendding.
> What is better:
> a) Create a field in "product" table and create a Trigger
> (before insert or update into vendding table) to alter this field.
> b) Create a vie
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Thursday, April 21, 2005 12:06 AM
> To: Tambet Matiisen
> Cc: KÖPFERL Robert; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Function to either return one or all records
>
>
> "Ta
>
> CREATE OR REPLACE FUNCTION getval(integer)
> RETURNS SETOF id_val_tbl AS
> $BODY$
> select * from id_bal_tbl where ( $1 is null )or (id=$1 ); $BODY$
> LANGUAGE 'sql' VOLATILE SECURITY DEFINER;
>
>
> It works fine, however an index is never used (if just one
> record is requested). The c
The problem in linked article could be solved with custom aggregate in
PostgreSQL:
create or replace function concat(text, text) returns text
immutable
language sql
as '
select case when $1 = then $2 else $1 || '' '' || $2 end
';
drop aggregate concat(text) cascade;
create aggregate c
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 16, 2005 5:42 PM
> To: Tambet Matiisen
> Cc: [EMAIL PROTECTED]; pgsql-sql@postgresql.org
> Subject: Re: [SQL] How to force subquery scan?
>
>
> "Tambet Mati
>
> "Tambet Matiisen" <[EMAIL PROTECTED]> writes:
> > How tough it would be to implement
>
> > CREATE VIEW xxx PARAMETERS (yyy) AS zzz;
>
> > as
>
> > CREATE TYPE xxx;
> > CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql
...
>
> Does this do better:
>
> SELECT
> prodid,
> avg(prodcost) as average_cost,
> sum(prodcost * salesamount) as cost_total
> FROM (
> SELECT
> p.product_id as prodid,
> product_cost(s.product_id, s.date) as prodcost,
> s.amount as salesamount
> FROM products p
> LEFT
Another (possibly design?) problem of mine. I have a function
product_cost(product_id, date), which does simple SELECT call. I
declared it as STABLE, hoping that multiple invocations of the same
function are replaced with one. Query is something like this:
SELECT
p.product_id,
avg(product_
...
>
> I wonder if it could be possible to improve CREATE VIEW
> syntax by adding parameters? Something like this:
>
> CREATE VIEW product_sales(date,date) AS
> select p.product_id, coalesce(sum(s.amount), 0)
> from product p
> left join sales s on p.product_id = s.product_id and s.date
> bet
>
> > Especially if you would like to put a filter on right side of left
> > join. Consider this query:
> >
> > select p.product_id, coalesce(sum(s.amount), 0)
> > from product p
> > left join sales s on p.product_id = s.product_id and s.date between
> > '2005-01-01' and '2005-01-31' group by
emembered.
Tambet
> -Original Message-
> From: PFC [mailto:[EMAIL PROTECTED]
> Sent: Saturday, March 12, 2005 2:08 PM
> To: Tambet Matiisen; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Parameterized views proposition
>
>
>
> What about using PREPARE ?
>
Hi there!
We use views in our applications a lot, in fact we prefer to have least
sql at client side. All queries are written as select * from view,
whenever possible.
But there are queries, which are impossible to express as views.
Especially if you would like to put a filter on right side of le
> -Original Message-
> From: Rafal Kedziorski [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 21, 2003 3:30 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] good style?
>
>
> hi,
>
> I have 8 tables and this query:
>
> select u.users_id, m.name as mandant_name, u.login_name, u.password,
I use Editplus (http://www.editplus.com/) with PosgreSQL syntax highlighting
(ftp://ftp.editplus.com/files/postgre.zip). I also define psql as external
tool in Editplus and use shortcut (Ctrl + 1) to send file to server.
Tambet
- Original Message -
From: "Eduardo" <[EMAIL PROTECTED]>
To
> -Original Message-
> From: pginfo [mailto:[EMAIL PROTECTED]]
> Sent: Saturday, February 01, 2003 3:50 PM
> To: Bruno Wolff III
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] update and IN vs. EXISTS
>
>
>
>
> Bruno Wolff III wrote:
>
> > On Sat, Feb 01, 2003 at 12:40:00 +0100,
> >
I think you should first check your application logic, for example print
something out just before calling the function. Then you can easily see, if
the problem is in PostgreSQL or in your application.
I once had similar problem, when I used function as argument to COALESCE.
COALESCE is translated
> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 23, 2003 7:01 PM
> To: Tambet Matiisen
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] calling function from rule
>
>
> "Tambet Matiisen" <[EMAIL PROTECTE
>
> "Tambet Matiisen" <[EMAIL PROTECTED]> writes:
> > I have a view and update rule on it, which updates another
> table. Now I would like to call a function, after update of
> the table is done. Also the function must be called in the
> same transa
I have a view and update rule on it, which updates another table. Now I would like to
call a function, after update of the table is done. Also the function must be called
in the same transaction as update. I tried to define an additional update rule on the
view, but doing a SELECT in update rul
> -Original Message-
> From: Marko Asplund [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, January 15, 2003 4:31 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Oracle outer join porting question
>
>
>
> i'm trying to port an existing application from Oracle8i to
> PostgreSQL but
> i'm havi
>
> If you need only not null-kdt_id and dor_id, then just change
> your joins
> into inner joins.
>
The whole idea is to show expected expenses (in "koostude_detailid") even if they were
actually not spent (the same material is not listed in "dokumentide_read"). And also
show actual expens
11:51 AM
> To: Tambet Matiisen
> Cc: [EMAIL PROTECTED]
> Subject: Re: [SQL] full join in view
>
>
> Tambet Matiisen wrote:
>
> > You are right. After disabling seq_scan, it uses indexes
> just as you
> > described. Unfortunately my view happens to use subqu
>
> Can you add some sql examples - table & index definition,
> view definition?
> If your view doesn't contain other views or sub-selects,
> postgres should
> use indexes.
> Tomasz Myrta
>
You are right. After disabling seq_scan, it uses indexes just as you described.
Unfortunately my view
I'm making a report in Crystal Reports, which makes use of full join. As Crystal
Reports does not support full join natively, I created a view which contains the join
and based my report on that view. The report has also a parameter to filter only
subset of rows from view. My problem is, that w
>
> When I'm trying to connect I have this error message:
>
> Something unusual has occured to cause the driver to
> fail.Please report this
> exception: java.sql.SQLException: Sorry, to many clients already.
>
I also met this error yesterday. Default PostgreSQL limit for incoming connections
In PostgreSQL 7.3 you have option to execute function with owner's rights or caller's
rights. Default is caller's rights (as it was before 7.3), you probably want owner's
rights. See development version of docs:
http://developer.postgresql.org/docs/postgres/sql-createfunction.html
btw, views "
27 matches
Mail list logo
