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
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 view or
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 21, 2005 12:06 AM
To: Tambet Matiisen
Cc: KPFERL Robert; pgsql-sql@postgresql.org
Subject: Re: [SQL] Function to either return one or all records
Tambet Matiisen [EMAIL PROTECTED] writes
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 column id
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
-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 Matiisen [EMAIL PROTECTED] writes:
It seems
...
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 JOIN sales s
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 AS 'zzz';
What's the point? It'd be nonstandard anyway, so just use
...
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
between $1
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,
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
-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 ?
---(end of broadcast
-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 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
-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 PROTECTED] writes:
Try 7.3, we changed the rules about
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
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 transaction as update. I tried to define an additional
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 expenses (in
-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 having
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 happens
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 subquery:
Don't disable seq_scan - sometimes it is better than
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
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 is
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
24 matches
Mail list logo
