from time_locations order by timestamp
loop
if last_location<>new_location then
return next;
end if;
last_location=new_location;
last_time=new_time;
end loop;
END;
$$ language 'plpgsql';
select * from location_changes();
--
Regards,
Tomasz Myrta
--
S
rt of the query.
I found two solutions:
- table A self join 1:1 in "from" part of query,
- table C subqueries in "where" clause - not too comfortable, because I
need col2 value several times.
Any other idea?
The database is PostgreSQL 8.1.5.
Regards,
Tomasz Myrta
ive results you need.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
how can i know the count of record in plpgsql.example if i'm query
with query komponent from delphi i will know the record count of
record.but how with plpgsql.
any ideas?
Is it what you need:
GET DIAGNOSTICS n = ROW_COUNT;
after executing query ?
Regards,
Tomasz
select * from table1 where itemkey>:lastvalue LIMIT x
Why do you complicate it so much? Everything you need is:
select * from table1 LIMIT x
select * from table1 LIMIT x OFFSET x
select * from table1 LIMIT x OFFSET 2*x
Remember to sort rows before using limit/offset.
Regards,
Tomasz My
uot; does not exist
You can't access column output alias in where clause. Instead you have
to use your function twice:
SELECT *, acl_check( objects.obid, ) AS mode FROM objects
WHERE acl_check( objects.obid, ) > 0;
Regards,
Tomasz Myrta
---(end of broadcast)---
row
being inserted/updated using NEW like this
Use OLD instead of NEW.
You can also use OLD inside UPDATE triggers - you have access to row
before update.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 9: the planner will ignore your desire
rom some_table
group by val having val>1;
ERROR: Attribute "val" not found
Is it a bug or a feature?
Regards,
Tomasz Myrta
PostgreSQL 7.3.4 on i386-pc-linux-gnu, compiled by GC
other
tables/columns. Is there a best way to write this query ?
Can you try this query ? :
select cd_area from area a1
join teacher_course c2 using (cd_course)
where (cd_teacher,cd_course) in (select 1,2 union select 98,45 union
select 11,0);
Regards,
Tomasz Myrta
---(end of
LT } [, ...] ) |
SELECT query }
insert into table (id, time, type)
select id, now(), '1' from secondtable;
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
NEXTVAL(\'seq_activities\'), '' || rec_recurrence.activity_id::text
|| '','' || rec_activity.activity_type_id::text
...
After this v_sql will be:
VALUES (
NEXTVAL(\'seq_activities\'),1,2,...
Regards,
Tomasz Myrta
---(end
up by date_trunc('day',some_time)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Regards,
Tomasz Myrta
---(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
Dnia 2004-02-13 10:14, Użytkownik Kumar napisał:
Thanks Tomasz Myrta. It is wonderful. I am still amazed from where you guys
knowing the options like quote_literal, etc.
Kumar
Just read the manual ;-)
6.4. String Functions and Operators
Tomasz
---(end of broadcast
ERROR: Bad timestamp external representation 'NULL'
It's because you can't use quotes with null. Valid query is:
insert into test(c1,c2) values (NULL, 'Hai');
Your dynamic query will then look like:
sqlstr := 'insert into test(c1, c2) values (' ||COALESCE(&#x
ake sure you have strings everywhere:
sqlstr := 'insert into test(c1, c2) values
('||COALESCE(rec.c1::text,'NULL')||','||'\''||rec.c2||'\')';
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
' ||ISNULL(rec.c1,'0')||','
||'\''||rec.c2||'\')';
Substitute it with NULL value:
sqlstr := 'insert into test(c1, c2) values(' || coalesce(rec.
le aliases.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ll have an
uncongruent recordset.
Well - you didn't read the chapter I noticed you, did you?
Look at function now(). It returns always the same value inside
transaction. If your current_period_id() works the same way as now()
then declare it as STABLE.
Regards,
Tomasz Myrta
---
TABLE. (look at CREATE FUNCTION documentation)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
year. Please let me know
now()+'1 year'::interval
I suggest you reading more about interval datatype in documentation.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
"parts"
Rewrite your query and show your results.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
form described later."
Regards,
Tomasz Myrta
---(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
tName,
firstName,
SUBSTRING(term FROM 1 FOR 4),
CASE SUBSTRING(term FROM 5)
WHEN 'S' THEN 1
WHEN 'X' THEN 2
ELSE 3
END;
or the easier one:
order by lastname,firstname, translate(term,'SXF','123');
Regards,
) as your_date) x;
Everything you need is to execute query above as many times as you need.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Dnia 2004-01-21 10:37, Użytkownik Tomasz Myrta napisał:
Use this integer sequence and interval datatype to get date result:
your_date='1994-01-01'::date+'1 day'::integer * time_key
^^^
Sorry, use interval here.
R
Jan 2
> 2000 1 Jan 1
Use this integer sequence and interval datatype to get date result:
your_date='1994-01-01'::date+'1 day'::integer * time_key
Now you can do whatever you want with this date - look at Postgresql
documentation "6.8. Date/Time Func
before* data output (and before
column aliases). You can still use column aliases in "GROUP BY" and
"ORDER BY".
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
om
(select * from table1 union
select * from table2) x;
Another way to eliminate such duplicates is creating simple pl/pgsql
insert trigger which checks dups before inserting new rows and returns
NULL if some row already exists.
Regards,
Tomasz Myrta
---(end of
reated SELECT is
to use the FOR-IN-EXECUTE form"
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
messa
hether your trigger has been fired as insert or
update trigger.
DECLARE
old_orderid integer;
BEGIN
if TG_OP=''UPDATE'' then
old_orderid=OLD.orderid;
else
old_orderid=-1;
end if;
...
Regards,
Tomasz Myrta
---(end of broadcast)--
Dnia 2003-12-04 19:09, Użytkownik Wei Weng napisał:
I want to convert an interval (from substraction between two timestamps) into
a integer that represents how many seconds that interval has. How do I do
that?
select extract(epoch from your_interval);
Regards,
Tomasz Myrta
other ones:
select comma(full_name) from...
There is one problem with this function - strings order is unexpectable,
but you can always sort them in subselect before using this function:
select comma(full_name) from
(select full_name from order by full_name) X;
Regards,
Tomasz Myrta
-
if;
return $1;
end;
' language 'plpgsql';
drop aggregate comma(varchar) cascade;
create aggregate comma (basetype=varchar, sfunc=comma_aggregate,
stype=varchar, initcond='' );
Regards,
Tomasz Myrta
---(end of broadcast)--
fixing the problem noted 24-Oct-02 by Hans-Jürgen
Schönig.
Yes, this is a description of my problem. Thanks a lot.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unreg
Dnia 2003-11-28 20:52, Użytkownik Tom Lane napisał:
Tomasz Myrta <[EMAIL PROTECTED]> writes:
If I add "and k.id_trasy=lt.id_trasy" into where clause (duplicate), the
query works fine.
Define "works fine", please (again, EXPLAIN ANALYZE would be a nice
concrete desc
508)
-> Seq Scan on linia_trasy lt (cost=0.00..1.14 rows=14
width=12) (actual time=0.02..0.12 rows=14 loops=1)
If I add "and k.id_trasy=lt.id_trasy" into where clause (duplicate), the
query works fine.
Regards,
Tomasz Myrta
---(end of broadc
ffsetid=o.offset_id and
e.event_date=g.begindate+o.offset_value)
where g.name='some_name' and e.offsetid=o.offset_id;
Join Filter: ("outer".event_date = ("inner".begindate +
"inner".offset_value))
Nested Loop...
Join Filter: ("outer&quo
tion in manual:
6.7. Data Type Formatting Functions
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
Dnia 2003-11-17 18:00, Użytkownik Michele Bendazzoli napisał:
p.s. I know, I'll have to begin to use the explain command ...
I promise I'll do it ;-)
Use the explain analyze command and then answer yourself ;-)
Regards,
Tomasz Myrta
---(end of
doesn't matter whether it is
executed as single or several statements.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
n key constraint (not too easy without a
constraint name) and recreate it as "DEFERRABLE", this query works fine
also in psql.
My question is: Why my query works fine when using pgAdmin, and it fails
when using psql?
Regards,
Tomasz Myrta
---(end of broadcas
.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
j_category is null
group by ca_code,ca_desc,ca_dis_cycle
Probably you also need to change your sum(j_amount) into:
sum(case when j_amount is null then 0 else j_amount end)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 3: if posting/reading through
me to find what really
happened. I was wondering if it is possible to know the real reason of
restart and leave some message in syslog just before postgres restarts.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at o
Dnia 2003-10-30 12:32, Użytkownik Tomasz Myrta napisał:
After few seconds all backends were disconnected and postgres restarted.
It didn't help :-( Even reboot didn't help...
Postgres is 7.3.2-2 on Debian Woody.
Sorry for my panic. It was my C function which raised segmentation faul
] ^IPlease reconnect to the database system
and repeat your query.
After few seconds all backends were disconnected and postgres restarted.
It didn't help :-( Even reboot didn't help...
Postgres is 7.3.2-2 on Debian Woody.
Regards,
Tomasz Myrta
---(end of
older than 7.x (7.3?)
You can't drop column this way. In older versions you can do this only by:
create table temp (columns as you wish to have)
insert into temp select * from old_table
drop old_table
alter table temp rename to old_table
Regards,
Tomasz Myrta
---(e
7;t work at all). You can use
"cascade" when dropping column. After this you need to recreate views
dropped together with a column.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
);
update institution set tmp=name;
alter table institution drop column name;
alter table institution rename tmp to name;
(or something like this)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http
can find it in documentation - 6.7. Data Type Formatting Functions
select to_char(departure,'DD-MM- HH24:MI:SS') ...
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
Hi
I have two functions:
A) function defined with "SECURITY DEFINER"
B) function defined with "SECURITY INVOKER"
Function A calls function B.
How is the function b called - with rights of definer of function A, or
rather with rights of caller of function A ?
R
.13145.43 rows=11167
width=1344) (actual time=0.22..1316.10 rows=9432 loops=1)
I wouldn't expect too much from query, which starts joining over 10k
rows and returns over 6 rows. Do you really need such a big result?
Regards,
Tomasz Myrta
---(en
left join A_SKLAD S on(d.IDS=s.IDS_DOC)
left join A_MED M ON(S.IDS_MED=M.IDS)
where d.IDS='SOF_700060'
?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 8: explain analyze is your friend
-
it should help. Anyway, what versions your Postgresql rpm's have?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Hello,
What is wrong with this function because the mytime variable contain the
same value in each iteration:
Just like it should be.
now() gives you always the same value inside single transaction. If you
need time for performance tests - use "timeofday".
Regards,
To
from some_interval)/3600/24::integer
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
value.
But DBMS returns me message like that
ERROR: You can't change sequence relation seq_id_seq
How to do this
Documentation says:
6.11. Sequence-Manipulation Functions
select setval('some_sequence',333);
Regards,
Tomasz Myrta
---(end of broadcast)
resultset
order by team asc, points desc
Regards,
Tomasz Myrta
---(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
o find some useful
functions and macros.
Regads,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
T $1 "
This error is raised when trying to execute SPI_cursor_fetch. What does
it mean? What does the SPI_prepare have to already opened cursor?
Where can I find better SPI documentation than "Postgresql Server
Programming" ?
Regards,
Tomasz Myrta
lpgsql: ERROR during compile of sample near line 2
ERROR: parse error at or near ";"
Can any body tell me why is this error coming
Really?
I got no error and select sample('nothing',3) gave mi 'ing'
What is your postgresql version?
Regards,
Tomasz
mestamp without time
zone but expression is of type text
You will need to rewrite or cast the expression
Use syntax above.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
; or "Gibe". It attacks (as usual) Internet Explorer without proper
patches.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
and
it's growing :-(
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
...
Regards,
Tomasz Myrta
---(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
Microsoft "Dear Customer... " based on www.microsoft.com design.
Both mails contains some .exe attachement.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
ould be a C function, but SPI
scares me...
And one more question - which syntax is valid?
move backward..
or
execute ''move backward...
Or try 7.4 beta ...
Currently stable branches are better for me...
regards, tom lane
Regards,
Tomasz Myrta
---(end of broa
lso no valid result.
What should I do with it?
Or maybe is it possible somehow to use cursor in FOR..IN loop?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
instead of 30608
2. to_date('030608','YYMMDD');
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Hey,
I've searched for MD5 crypting function in PG, but I did not find it.
Anyone knows how to implement this function in PG ?
Best wishes,
Marek L.
Use contrib/pgcrypto
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 3: if posting/re
f1
t1.f1,
sum(t2.f1) as sum_f1,
avg(t2.f1) as avg_f1,
count(*) as pseudo_recno
from
ttt t1
join ttt t2 on (t2.f1<=t1.t1)
group by t1.f1
order by t1.f1;
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 9: the planner will ignore your des
Hello,
I've got a question about "LIKE" phrase. I have such a phrase:
LIKE UPPER(Key_Zawod)
And I want to write after Key_Zawod % but it doesn't work. Is there any special way to code this phrase?
like upper(key_zawod) || '%'
Regards,
Tomasz Myrta
--
I need a conversion.
Any suggestions on how to accompish this? Simpler the better, of course.
extract(epoch from some_interval)/3600
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
ort ce using (userid,category_id)
where el.userid=21742 and size ilike '%WEBFL%' and ce.userid is null
and el.userid=eyp_listing.userid
and el.category_id=eyp_listing.category_id);
It should be solution, but not the answer for your question...
How did you use that sub-select, so it didn
Hi Folks,
DELETE from eyp_listing where userid=t_a.userid and category_id=t_a.category_id;
such queries work perfectly.
but if t_a is a subquery how to accomplish the delete.
What kind of subquery it is? Exist/Not exist doesn't work?
Regards,
Tomasz Myrta
---(e
Hi there,
I have a problem calculating a date. A field carries the date as passed
seconds since Jan 1st 1970.
How can I get the date as dd.mm. out of this??
cast('1970-1-1' as timestamp)+cast(your_ticks || ' seconds' as interval)
Does anyone know better way to cast it?
R
GROUPed or used in an aggregate function
=#
Watch out.
When using outer joins you get NULL values.
some_text || NULL = NULL (always)
Try this:
select r.rtid,
concat(r.rcid::text || coalesce(' on ' || l.lnumber::text,'')
as task
from rides r
left outer join loco_dets
/pg_dump
/usr/bin/pg_dump
/usr/local/pgsql/bin/pg_dump
*
Probably you have both 7.2.2 and 7.3.3 pg_dump versions and PATH points
to 7.2.2 /usr/bin/pg_dump. Try using /usr/local/pgsql/bin/pg_dump
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 6:
not exist
dlm=#
Too many dots?
Shouldn't it be "new.project_id"?
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
also for some nr_proponente returns always the same
value (look at IMMUTABLE description)
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Dnia 2003-06-30 23:13, Użytkownik Tomasz Myrta napisał:
Hi
Do you have any easy script to remove all pl/pgsql function? After a lot
of changes inside "create or replace function..." scripts I have a big
mess. I want to remove all user defined pl/pgsql functions and restore
some of th
Hi
Do you have any easy script to remove all pl/pgsql function? After a lot of
changes inside "create or replace function..." scripts I have a big mess. I
want to remove all user defined pl/pgsql functions and restore some of them
from my scripts again.
Regards,
To
Dnia 2003-06-27 16:07, Użytkownik Együd Csaba napisał:
Tomasz,
it seems to be interesting for me as well, but actually I can't download a
file from the given url.
I just click on the binary zip link, but nothing happen. How should I click?
:)
Thanks,
There is nothing special - it's a default source
Dnia 2003-06-27 12:25, Użytkownik [EMAIL PROTECTED] napisał:
I think I looked at zeos at some point and found that it did not support
md5 authentication, which, at the time, I thought was important.
~Berend Tober
Zeos has nothing to authentication. It's a matter of libpq library, which
currently
gt;
> Thanks
>
> Murali
http://sourceforge.net/projects/zeoslib
Nice solution - your executable needs only small libpq.dll file without any
annoying BDE/ODBC installation.
There is one more native driver for C++ Builder/Delphi - pgexpress, but it's
not free.
Regards,
Tomasz Myr
Dnia 2003-06-24 00:04, Użytkownik Tomasz Myrta napisał:
select package_name,
count(*) as n_packages
from
packages
join package_log using (package_id);
And one more:
select package_name,
sum(case when package_log.package_id is not null then 1 else 0 end)
as n_packages
from
packages
left
package_id=p.package_id)
as n_packages
from packages p;
And one more:
select package_name,
sum(case when package_log.package_id is not null then 1 else 0 end)
as n_packages
from
packages
left join package_log using (package_id);
Regards,
Tomasz Myrta
---(end of
the end into simple connections. Maybe I should
dig into this problem again and rewrite my queries...
Regards,
Tomasz Myrta
---(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
ull.
I was thinking about something more comfortably - table without pre-declared
columns so I don't have to change table definition when pl/pgsql function changes.
Can anyone tell me how triggers work? Do they need to look into table column
definition to retrieve data?
Regard
from table2.
Data from table1 are transferrend into table2 using triggers. I found, I don't
to have any data in table1.
The question is: Is it possible to create virtual table in Postgresql? Virtual
- means it won't contain any columns nor data, but trigger doing all the job.
Regard
Dnia 2003-06-20 07:12, Użytkownik Együd Csaba napisał:
Hi All,
here are my three tables. I wold like to list them in the following way: I
need all the columns from t_stockchanges, and one field for the
productgroup
the t_stockchanges.productid belongs to.
But one product can belong to many gro
quot;limit" and "offset" are well described in Postgresql documentation.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Dnia 2003-06-17 17:20, Użytkownik Rado Petrik napisał:
Hi,
How I create table1 from other table2 .
"cp table1 table2"
create table2 as select * from table1;
It is described in Postgresql documentation SQL Commands -> "create table as"
Regards,
Tomasz Myrta
---
null" or "primary key" constraint). This kind of violation
raises exception and whole transaction is aborted. I don't use java, but C
librares raises also ordinary C exception which can be easily caught. If you
want to avoid such cases - check your data before inserting them
tribute or function 'oid'"
When I try to access views from ACCESS2000 there is no problem
What is the cause of this? Can I resolve it?
Thanks and regards
Javier
"oid" is a table field. If you create view and you don't expose oid from
any table - you loose acce
how can i avoid this loop ?
For this case creating trigger would be much better than rule.
I think, you can't use rules this way.
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http:
ckage. After copying this file
from source tgz, I get only one line result:
@configure@
Regards,
Tomasz Myrta
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Dnia 2003-06-16 16:53, Użytkownik Tom Lane napisał:
Tomasz Myrta <[EMAIL PROTECTED]> writes:
psql (PostgreSQL) 7.3.2
SELECT cast(cast('1 day 12 hours' as interval) as time);
time
--
00:00:00
(1 row)
I get 12:00:00 here, using 7.3.3 ... platform-specific problem maybe
looked into 'history' file, but I couldn't find
anything interesting about changes in casting interval into time.
I found replacement for this problem, but I wan't just to know what
happened:
('1970-1-1'::date+some_interval)::time
Regards,
Tomasz My
1 - 100 of 202 matches
Mail list logo
