I have a table called 'calls' which holds 'call detail records'. Let's
assume the table looks like this:
CREATE TABLE cdr (
call_id serial,
phone_number text
);
And I have a table with country call prefixes, that looks like this:
CREATE TABLE prefixes (
prefix text,
On 12/05/2010 05:57 PM, Mario Splivalo wrote:
The issue in both approaches is that if I have two product_ids that are
viewed same number of times and share the first place as most viewed
products by that user, I'll get only one of them (LIMIT 1 OR MAX() can
only return one row :).
And
On 12/03/2010 12:40 PM, Jayadevan M wrote:
Hello,
I went this way, but for a large number of user_id's, it's quite slow:
CREATE VIEW v_views AS
SELECT user_id, product_id, count(*) as views
FROM viewlog
GROUP BY user_id, product_id
SELECT
DISTINCT user_id,
(SELECT product_
On 12/03/2010 12:40 PM, Jayadevan M wrote:
Hello,
I went this way, but for a large number of user_id's, it's quite slow:
CREATE VIEW v_views AS
SELECT user_id, product_id, count(*) as views
FROM viewlog
GROUP BY user_id, product_id
SELECT
DISTINCT user_id,
(SELECT product_
I have a log-table where I record when some user_id has viewed some
product_id:
CREATE TABLE viewlog (
user_id integer,
product_id integer,
view_timestamp timestamp with time zone
)
Now, I would like to get result that gives me, for each user_id,
product_id of the produ
Tom Lane wrote:
I think what Mario is actually complaining about is that partial unique
indexes are not part of the SQL standard, and he wants a solution that
at least gives the illusion that it might be portable to some other
RDBMS in the future.
Correct. As far as I can see there is no parti
Jasen Betts wrote:
> ...
>
>> The 'proper' way to do this (as suggested by earlier posts on this
>> mailing list) is to use partial UNIQUE indexes, but I have problem with
>> that too: indexes are not part of DDL (no matter that primary key
>> constraints and/or unique constraints use indexes to e
The 'data integrity' rule for database I'm designing says that any
subject we're tracking (persons, companies, whatever) is assigned an
agreement that can be in several states: 'Approved', 'Unapproved' or
'Obsolete'. One subject can have only one (or none) 'Approved' or
'Unapproved' agreement, and
I have an enum-type, like this:
CREATE TYPE type_enum_service_type AS ENUM
('Banner', 'Ticker', 'Memo');
Then I have a table, like this:
CREATE TABLE services (
service_id integer NOT NULL,
service_type type_enum_service_type NOT NULL,
service_keyword character varying NOT NULL,
servi
I looked at the information_schema.routines, to get information about
the functions in the database, but there doesn't seem to be a way to
extract the parameters information about functions? Where would I seek
for such information?
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postg
As I have discovered, there is no way to just create index on a column
of type 'point' - postgres complains about not knowing the default
operator class, no matter what index type I use.
Now, my table looks like this:
CREATE TABLE places (
place_id integer primary key,
coordina
Tom Lane wrote:
> Mario Splivalo writes:
>> I have two tables, tableA and tableB:
>> CREATE TABLE tableA (idA integer primary key, email character varying
>> unique);
>> CREATE TABLE tableB (idB integer primary key, email character varying
>> unique);
>
>&
How would you do it, without creating third table?
Mario
Ries van Twisk wrote:
> can't you solve it creating a reference between the tables?
>
> Ries
> On Sep 14, 2009, at 8:24 AM, Mario Splivalo wrote:
>
>> I have two tables, tableA and tableB:
>>
>
I have two tables, tableA and tableB:
CREATE TABLE tableA (idA integer primary key, email character varying
unique);
CREATE TABLE tableB (idB integer primary key, email character varying
unique);
Now, I want to create check constraint in both tables that would
disallow records to either table whe
Marcin Krawczyk wrote:
> Hi, I believe you're looking for invalid_text_representation.
>
> EXCEPTION WHEN invalid_text_representation THEN
>
Thnx, that is it.
Mike
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql
Is there a way to trap this error in plpgsql code?
I have a function that accepts integer and character varying. Inside
that function I need to cast that varchar to integer. Of course,
sometimes that is not possible.
When I run function like that, I get this errror:
fidel=# select * from get_acco
Tom Lane wrote:
> Mario Splivalo writes:
>> But, date_trunc behaves like round function: round(1.9) = 2.
>
> Hmm ... only for float timestamps, and only for the millisec/microsec
> cases.
>
> case DTK_MILLISEC:
> #ifdef HAVE_INT64_TIMESTAMP
>
It's stated in the docs that date_trunc is "conceptually similar to the
trunc function for numbers.".
So, trunc(1.1) = 1, trunc(1.9) = 1, and so on.
But, date_trunc behaves like round function: round(1.9) = 2.
Example:
idel=# select date_trunc('milliseconds', '2009-01-01
12:15:00.000999+02'::ti
I have a table where there are actinos for some user logged. It's part
of the MPI system of some sort. For every user, action type and time of
the action is logged. There are many action types but the ones which are
of interest to me are BEGIN and END. I need to find the durations for
all the p
landsharkdaddy wrote:
I have not tried that but I will in the morning. The @ in SQL is used to
indicate a parameter passed to the query. In PostgreSQL it seems that the :
is the same as the @ in SQL Server. I tried something like:
SELECT * FROM Customers WHERE FirstName LIKE :custfirst + '%';
Mikel Lindsaar wrote:
Hi all,
Doing some googling and looking through the docs, I can't find an
obvious way to do this beside post processing after the query (which I
am trying to avoid).
I'm trying to select a list of names in alphabetical order but
ignoring the whitespace.
So for example, th
Dirk Jagdmann wrote:
When you need to choose between enum types, domain types or lookup tables
with foreign keys, what do you usualy choose?
When I have a column with valid values that I know when writing my
tables and that will *never* change I use an enum. For example a human
gender type (and
When you need to choose between enum types, domain types or lookup
tables with foreign keys, what do you usualy choose?
Only recently I started using domains, but that seems to be painful, as
Joshua Drake explains here:
http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_
Is there a way to get recordset out of XML document - something similair
to contrib/xml2's xpath_table function, but I'd like to be able to get
data out of XML document that is not stored in the database.
I wrote my own plpgsql function for converting XML document data to
recordset (table), bu
Bart van Houdt wrote:
Hi all,
This might be a stupid question, but I wasn't able to find any information on
it, so here it goes:
Oracle knows a view which contains information about indexed columns
(all_ind_cols), is there something similar available in Postgres?
I want to be able to create a
Mario Splivalo wrote:
But, if I want it other way around, I get the error:
test1=# select envode(E'\305', 'hex');
ERROR: invalid byte sequence for encoding "UTF8": 0xc5
HINT: This error can also happen if the byte sequence does not match
the encoding ex
Tom Lane wrote:
Mario Splivalo <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
Exactly what version of pg_dump are you using? What I get from pg_dump
doesn't look like that. Bytea fields with -D look more like this:
INSERT INTO foo (f1) VALUES ('\\305S\\224\\226\\203)')
Richard Huxton wrote:
Mario Splivalo wrote:
Richard Huxton wrote:
Mario Splivalo wrote:
I have this issue:
postgres=# select E'\xc5\x53\x94\x96\x83\x29';
ERROR: invalid byte sequence for encoding "UTF8": 0xc553
I think you want to be using octal escapes. That's tex
Tom Lane wrote:
I'm using the above mentioned string to store data into bytea column. I
did pg_dump of the database on postgres 8.2, and then tried to restore
it on postgres 8.3, and I got this error. The actuall line that produces
error is like this:
INSERT INTO vpn_payins_bitfield (vpn_id,
I have a table, like this:
CREATE TABLE t1
(
u1 character varying
)
And some data inside:
INSERT INTO t1 (u1) VALUES ('62d6b434-7dfd-4b3b-b1bf-87f6c20c10dd');
INSERT INTO t1 (u1) VALUES ('e3fee596-164b-4995-9e0d-7b2a79e83752');
INSERT INTO t1 (u1) VALUES ('37a42ec8-9000-44bc-bb06-13b5d4373a45
I have this issue:
postgres=# select E'\xc5\x53\x94\x96\x83\x29';
ERROR: invalid byte sequence for encoding "UTF8": 0xc553
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
postgres=# show clien
Tom Lane wrote:
Mario Splivalo <[EMAIL PROTECTED]> writes:
And here is the 'problematic' query:
melem=# select * from t2 where id1 in (select id1 from t1);
I guess postgres should tell me that column name id1 is nonexistant in
table t1.
No, it shouldn
I have two tables, 'configured' like this:
melem=# \d t1
Table "public.t1"
Column | Type| Modifiers
+---+---
id | integer |
value | character varying |
melem=# \d t2
Table "public.t2"
Column | Type
On Thu, 2007-01-25 at 11:09 -0500, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > Am I doing something wrong here, or there is no way of using temporary
> > tables within 'sql' written functions?
>
> I believe the problem is that for a SQL f
On Thu, 2007-01-25 at 11:00 -0500, Andrew Sullivan wrote:
> On Thu, Jan 25, 2007 at 03:39:14PM +0100, Mario Splivalo wrote:
> > When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
> > as a function language), I can't because postgres can&#
On Thu, 2007-01-25 at 08:03 -0800, Stephan Szabo wrote:
> On Thu, 25 Jan 2007, Mario Splivalo wrote:
>
> > When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
> > as a function language), I can't because postgres can't find that
>
When I try to use TEMPORARY TABLE within postgres functions (using 'sql'
as a function language), I can't because postgres can't find that
temporary table. Consider this example:
CREATE FUNCTION func1() RETURNS SETOF v_messages_full AS $BODY$
CREATE TEMPORARY TABLE tmpTbl
AS
SELECT
messag
On Tue, 2006-09-05 at 11:22 -0400, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > So, I guess it's obvious that postgres doesn't treat regular expressions
> > the same way as java/perl/pyton/php/awk/sed do...
>
> When you get into stuff a
On Tue, 2006-09-05 at 10:21 -0400, Alvaro Herrera wrote:
> Mario Splivalo wrote:
> > On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote:
> > > On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote:
> > >
> > > pulitzer2=# select 'sto
On Tue, 2006-09-05 at 08:42 -0500, Aaron Bono wrote:
> On 9/5/06, Mario Splivalo <[EMAIL PROTECTED]> wrote:
>
> pulitzer2=# select 'stop works' ~ '^\s*(?:[\
> +|-]|(?:[sS][tT][oO][pP]\b)).*$';
> ?column?
>
On Tue, 2006-09-05 at 10:11 -0400, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > Now, here is what happens if I try this in postgres:
>
> > pulitzer2=# select '+mario' ~ '^\s*(?:[\+|-]|(?:[sS][tT][oO][pP]\b)).*$';
>
> I'
[sS][tT][oO][pP]\b)).*$';
?column?
--
t
(1 row)
This one is also ok.
pulitzer2=# select 'mario works' ~ '^\s*(?:[\
+|-]|(?:[sS][tT][oO][pP]\b)).*$';
?column?
--
f
(1 row)
Same as this one, also ok.
pulitzer2=# select 'stop works' ~ '^\s*(?:[\
+|-]|(
;
l_netsms=#
This is expected, I'm just unsure why ain't I receiving that error when
running punimessages() function?
Postgres is 8.1.2 running on linux 2.6.17.
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick
On Fri, 2006-06-30 at 21:47 +0900, Michael Glaesemann wrote:
> On Jun 30, 2006, at 21:35 , Mario Splivalo wrote:
>
> > Is postgres going to make one query plan, or each view has it own
> > query
> > plan?
>
> Have you taken a look at the EXPLAIN ANALYZE output o
ry plan, or each view has it own query
plan?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 6: explain analyze is your friend
encrypted passwords.
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an
On Tue, 2006-05-02 at 09:13 +0100, Richard Huxton wrote:
> Mario Splivalo wrote:
> > I have found, I thinl, in the pg manual, the way to get the number of
> > rows inserted/updated, from within the plpgsql. I can't find it anymore,
> > is that still there, or I misread so
I have found, I thinl, in the pg manual, the way to get the number of
rows inserted/updated, from within the plpgsql. I can't find it anymore,
is that still there, or I misread something earlier?
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do i
Comming back after SELECTing into usertypes, is there a nice way to
insert data into table from usertype, in a nice way?
For instance, I have table like this:
CREATE TABLE tblA (
id int4,
key varchar,
value varchar
)
Then, in my function, I do this:
CREATE FUNCTION testI
On Tue, 2006-04-25 at 14:21 +0200, A. Kretschmer wrote:
> am 25.04.2006, um 14:03:07 +0200 mailte Mario Splivalo folgendes:
> >
> > And then, when I want to fill in the type_var, i do this:
> >
> > type_var.member_a := col_a FROM table_a WHERE col_c = 5;
> &g
27;d like to do?
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
On Thu, 2006-02-23 at 17:35 +0100, PFC wrote:
>
> > Now, If some other user want's his prize, when checking his code, if he
> > sends code for some other service then service 1, that's ok. If he's
> > sends code for the service 1 the PERFORM will wait untill I'm finished
> > with previous user.
>
On Thu, 2006-02-23 at 13:56 +0200, Achilleus Mantzios wrote:
> The intersection of rows that satisfy BOTH
> "code_id = 1 AND code_value = 'abla'"
> and
> "code_id = 1 AND code_value = 'eble'"
> is ZERO!!!
>
> Why would you want irrelevant rows to wait for one another??
It was a bit silly repre
On Wed, 2006-02-22 at 13:58 -0500, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > Now, when I do this from one connection (psql shell, for instance):
>
> > [A]BEGIN TRANSACTION;
> > [A]SELECT * FROM bla WHERE code_id = 1 FOR UPDATE;
>
>
UPDATE
I will get the rows.
If I erase the index bla_idx1, then [C] select will wait, same as [B]
select will wait. Is there a way to have this behaviour with the index
on column code_id?
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I c
On Mon, 2006-02-13 at 17:10 +0100, Mathieu Arnold wrote:
> | It works like this:
> |
> | ORDER BY (CASE WHEN 5=5 THEN "from"::varchar ELSE
> | receiving_time::varchar) DESC.
> |
> | Is there a way to have DESC/ASC inside of a CASE?
> |
> | Mario
>
> No, you don't understand, you should do s
EN 5=5 THEN "from"::varchar ELSE
receiving_time::varchar) DESC.
Is there a way to have DESC/ASC inside of a CASE?
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
END, or do I need to have two
querries, and then first check for the value of the parametar, and then,
according to that value, call the SELECTs wich sort randomly, or by
receiving_time.
Mario
P.S. The postgres is 8.1.2.
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick,
On Tue, 2006-01-31 at 12:30 +, Richard Huxton wrote:
> Mario Splivalo wrote:
> > When I create a view, I like to define it like this (just representing
> > the form here):
> [snip]
> > But, when I extracit it from postgres, it's somehow stored like this:
> [
ostgres NOT to format the 'source code' of my
views?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 1: if posting/readi
On Fri, 2006-01-27 at 04:48 -0800, Emil Rachovsky wrote:
> Hi,
> I am using PostgreSQL 8.1.0 . How can I find a temp
> table from my session, having the name of the table?
> Can anyone show me what query should I execute? I've
> tried some things but I receive mixed results of
> tables from differe
"NOTIFY interacts with SQL transactions" in the NOTIFY documentation.
I've been playing with that, but performance drops significantly with
those notifications. And, it would be a hack. We redesigned the process
so it makes sense :)
>
> I don
On Wed, 2006-01-25 at 15:54 +0100, Markus Schaber wrote:
> Hi, Mario,
>
> Mario Splivalo wrote:
> > Is it possible to change the transaction level within the procedure?
>
> No, currently not, the PostgreSQL "stored procedures" really are "stored
> functio
On Wed, 2006-01-25 at 11:46 -0500, Jaime Casanova wrote:
> you need to set the transaction level after the begin and before every
> other statement... after the begin you have a select that invoke your
> function so that set is not the first statement...
But I can't do that inside of a function,
or I need to do 'SET
TRANSACTION ISOLATION LEVEL TO SERIALIZABLE' before step 2 in both
thread A and thread B?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of
oup. Is there a way to extract 'auction
24' as a second group?
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 9: In versi
On Wed, 2006-01-04 at 10:00 -0700, Michael Fuhr wrote:
> What Andreas is saying is that the patterns in the table have too
> many backslashes. The original query was
>
> test=> select '+385911234567' ~ '^\\+38591\\d{7}$';
> ?column?
> --
> t
> (1 row)
>
> but if you select just the p
turn "t", esp. when I wrote the first query it seems that the regex
match was ok.
Or I can't do regexp matching from the table columns?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
s the general feeling concerning the uuid issue I explained above?
I've never written a C postgreSQL function, and any help (or
documentation pointout) would be greatly appreciated.
If I posted this to the wrong mailing list, please point me out to the
correct one.
Mario
--
Mario Spl
On Tue, 2005-12-06 at 09:58 -0500, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > Now I want all services which didn't have any messages within certain
> > period:
> > pulitzer2=# select * from services where id not in (select distinct
&g
EEN '2005-10-01' AND '2005-10-30'
GROUP BY
services.id,
services.keyword
ORDER BY
services.keyword
This query runs MUCH faster, but it omits the 'cocker' column, as if I
used INNER JOIN.
Any clues? I'm stuck here...
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
On Sat, 2005-11-19 at 20:03 -0700, [EMAIL PROTECTED] wrote:
> Hello all,
>
> I'm trying to create a schema that contains the default tables,
> functions, etc. of the public schema. Using pgAdmin, when I create a
> schema, it is blank - has no associated aggregates, tables, etc. How
> would I cre
On Wed, 2005-11-09 at 17:05 -0500, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > Works fine. I remind you again, this is on a newly created database.
>
> Yup, works fine for me too.
>
> > When I run it, again the same:pulitzer2=# select * from
&g
On Wed, 2005-11-09 at 13:49 -0500, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > When I do:
> > SELECT * FROM get_ads(1004);
> > i get:
> > ERROR: query-specified return row and actual function return row do not
> > match
>
> Th
I do:
SELECT * FROM get_ads(1004);
i get:
ERROR: query-specified return row and actual function return row do not
match
Why is that?
Mike
P.S. That's run on Postgres 7.4.
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap,
seems that SELECT itself is slow, not the currval.
Am I doing something wrong? Or that's just the way it is?
I have a workaround now, I declared local variable of type messages%ROW,
and I fill that variable with the parametars received from the caller of
the function, and then i do RETURN
described
it. I'd also lilke to see he's example of brute-force 'cracking' the MD5
digest.
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
ppose), or put it on a web page or
> whatever. General Bits is a fairly regular column that Elein Mustain
> puts out. I bet she'd include a submission on this topic, although
> you'd have to ask her. You can find GB at
> <http://varlena.com/varlena/GeneralBits/>.
I'll
On Thu, 2005-10-27 at 06:21 -0400, Andrew Sullivan wrote:
> On Thu, Oct 27, 2005 at 10:22:41AM +0200, Mario Splivalo wrote:
> > offers no replication at all, you need to use slony (wich is also a poor
> > replacement for a wannabe replication), or some other commercial
> > pro
slow down postgres
quite a bit.
So, to end this 'my father has bigger car than yours' debate, when will
postgres have two phase commit protocol implemented? I presume that
should come after you allow something like SELECT someCol FROM
myServer.myDatabase[Schema].myTable...
Mike
--
ation wos working as expected, and the load that replication posed
was insignificant. The only TRUE problem was that replication was
unidirectional. That SAME problem has Slony, and other 'replication
systems' available for postgres.
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED
On Wed, 2005-10-26 at 15:45 -0400, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > Wo-ha, makes perfect sense. So, I'd go by with declaring the rec as
> > varchar, instead as of a record. Wich is what I should do in the first
> > place.
>
> O
On Wed, 2005-10-26 at 10:40 -0400, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > biblio3=# select * from php_get_subfield_data_repeating(1,'606a');
> > php_get_subfield_data_repeating1
> > --
> > (Anđel
ering is this like that 'by
design', or is it a bug.
I've been reproducing this on postgres versions 7.4.8, 7.4.9, 8.0.3,
8.0.4 and 8.1beta3.
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
1,'606a');
subfield_data
---
Anđeli
ofsajd
(2 rows)
Am I doing something wrong here? Why do I need to create type with only
one member of type varchar to have results without the parentheses?
Mike
P.S. The subFieldValue field in the records_sub table is of type
On Fri, 2005-10-21 at 10:20 -0400, Tom Lane wrote:
> Mario Splivalo <[EMAIL PROTECTED]> writes:
> > I have a query that does COUNT, LEFT JOIN and GROUP BY from two tables.
> > One has 85000 records, and other has 100 records. I've been running
> > the tests o
just that when I run the query with pg7.4.8 it
takes 100% of the processor time while running. pg7.4.9 takes 2-10%
while running. Disk activity is much more intense with pg7.4.9
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well
4.9, and i'm running the very same
query on the very same database with all the indices and constraints
beeing the same (also SET ENABLE_SEQSCAN TO OFF), and I get around
90-110 seconds.
Has anyone noticed extreeme slowdown of postgres 7.4.9?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTE
On Wed, 2005-10-19 at 11:43 +0100, Richard Huxton wrote:
> Mario Splivalo wrote:
> > pulitzer2=# \t
> > Showing only tuples.
> > pulitzer2=# \f#
> > Field separator is "#".
> > pulitzer2=# select * from pg_user;
> >
es I have problems because data in my tables
often contain "|" charachter.
I've been looking trough the psql source code, but just like that, it's
too big hassle for me now :)
Am I doing something wrong?
Mike
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
he desired query, in your case:
FOR res IN EXECUTE SELECT x FROM test LOOP
...
It is more convinient to add the SQL query to a variable, and then exec
that variable, like so:
_SQL := 'SELECT a, b FROM tmpTable WHERE colText=' ||
quote_literal(someParametar);
FOR res IN EXECUTE _SQL LOOP
On Tue, 2005-10-11 at 15:39 +0100, Richard Huxton wrote:
> Mario Splivalo wrote:
> >
> > Of course, if it returns no rows, I'm presented with an error, saying:
> >
> > ERROR: record "rec" has no field "_qv"
> >
> > This is logic
no field "_qv"
This is logical. My question would be is there a way around this
withouth first doing SELECT COUNT(*) FROM tmbBids WHERE ..., and then if
COUNT(*) is zero THEN bidCount := NULL ELSE if COUNT(*) is not zero then
do SELECT from the begining?
Mike
--
Mario Splivalo
Mob
On Fri, 2005-09-30 at 02:34 -0600, Michael Fuhr wrote:
> On Fri, Sep 30, 2005 at 10:00:35AM +0200, Mario Splivalo wrote:
> > PgSql8.1beta2 gives me this:
> >
> > ERROR: EXECUTE of SELECT ... INTO is not implemented yet
> > CONTEXT: PL/pgSQL function "_rndb
PgSql8.1beta2 gives me this:
ERROR: EXECUTE of SELECT ... INTO is not implemented yet
CONTEXT: PL/pgSQL function "_rndbroj" line 5 at execute statement
Shall I forget about this once and for all (and use FOR construct), or
there is a chance that will be 'fixed'?
I can assign a value to a variable in several ways:
myVar := (SELECT col FROM table WHERE somecondition...)
myVar := col FROM table WHERE somecondtition...
SELECT col INTO myVar FROM table WHERE somecondition
How do I do any of the above using EXECUTE? I need to be able to assign
the value to a v
x27;t have postsgres handy now, I'll check that ASAP, but somehow I feel
that I'd run into same problems as before.
I'll try and I'll post a reply here.
Mario
--
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]
"I can do it quick, I can do it cheap, I can do it well. Pick
On Wed, 2005-09-28 at 10:01 -0700, codeWarrior wrote:
> >
> > ---(end of
broadcast)---
> > TIP 5: don't forget to increase your free space map settings
> >
>
> FOR rec IN EXECUTE ''SELECT COUNT(col1) AS num FROM tmpTbl WHERE col2
= " ||
> aBid || ''
I've learned that one can't use temporary tables within the function
unless
EXECUTE'd the SELECTS from that temp table.
So, I have a function like this:
CREATE FUNCTION Foo1(int4, int4) RETURNS SETOF myType
AS
'
DECLARE
aDataId ALIAS FOR $1;
aBid ALIAS FOR $2;
return myTyp
99 matches
Mail list logo
