Hi,
I would like to write some administration plpgsql scripts that populate some
tables (dimension tables) and to execute them using psql. I’m not sure that is
possible with psql as it is with Oracle sqlplus or SQL Server MSQuery:
Oracle sqlplus:
---
DECLARE
V_MyObjectID bigint;
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] De la part de John DeSoi
> Envoyé : lundi 16 janvier 2006 08:51
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Executing plpgsql scripts using psql, is
> If you want to use plpgsql it will need to be within a function. In your
> reply
> you mention creating user-defined functions as part of the set up
> procedure.
> It would not be weird to include the static data function as part of that
> procedure and then call it to load the data. I see no re
Hi,
Is there a way to define a function as a procedure, I
mean a function that returns nothing.
CREATE OR REPLACE FUNCTION foo()
AS $$
BEGIN
END;
$$ LANGUAGE 'plpgsql';
Actually, PostgreSQL complains as a “function
result type must be specified”. I can patch my function
Hi,
Is it possible to define a function with some default
values?
CREATE OR REPLACE FUNCTION foo(i IN int, j IN int
DEFAULT := 1)
…
Anyway, I didn’t find such a feature described in the
PostgreSQL 8.1 documentation, but sometimes, that doesn’t mean that the feature
doesn’t exis
> -Message d'origine-
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : dimanche 29 janvier 2006 10:48
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Function with default value?
>
> Daniel CAUNE <[EMAIL PROTECTED]> wri
lly, I’m not sure that is useful; perhaps PostgreSQL
handles pretty well such query using an index such as:
CREATE INDEX IDX_GSLOG_EVENT_PLAYER_EVENT_TIME_DESC
ON GSLOG_EVENT(PLAYER_USERNAME,
EVENT_NAME,
EVENT_DATE_CREATED);
Any idea?
--
Daniel CA
-scripts on Linux.
Thanks (Tom
Lane J)
--
Daniel CAUNE
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] De la part de Bruce Momjian
> Envoyé : mercredi, février 01, 2006 17:57
> À : Daniel Caune
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Does PostgreSQL support jo
> -Message d'origine-
> De : Owen Jacobson [mailto:[EMAIL PROTECTED]
> Envoyé : mercredi, février 01, 2006 18:00
> À : Daniel Caune; pgsql-sql@postgresql.org
> Objet : RE: [SQL] Does PostgreSQL support job?
>
> Daniel Caune wrote:
> > Hi,
> >
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] De la part de Alvaro Herrera
> Envoyé : mercredi 1 février 2006 19:28
> À : Daniel Caune
> Cc : Owen Jacobson; pgsql-sql@postgresql.org
> Objet : Re: [SQL] Does PostgreSQL supp
> > I'm not sure to understand. Why calling a function from a script is
> different from executing a series of SQL commands? I mean, I can run
a
> script defined as follows:
> >
> > SELECT myjob();
> >
> > where myjob is a stored procedure such as:
> >
> > CREATE OR REPLACE FUNCTION myjob()
> >
> Daniel Caune wrote:
> >>> I'm not sure to understand. Why calling a function from a script
is
> >>> different from executing a series of SQL commands?
>
> [snip]
> >>>Does that make sense?
> >>It does make sense if myjob() does
Hi,
Is there a way to force PostgreSQL using an index for
a SELECT statement? I just want to confirm that the index PostgreSQL decides
to use is better than the index I supposed PostgreSQL would use (I already
analyze the table).
Regards,
--
Daniel CAUNE
Ubisoft Online
> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> > Hi,
> >
> >
> >
> > Is there a way to force PostgreSQL using an index for a SELECT
> > statement? I just want to confirm that the index PostgreSQL decides
to
> > use is better than
> > > > Is there a way to force PostgreSQL using an index for a SELECT
> > > > statement?
> > >
> > > Your best bet is to do
> > >
> > > set enable_indexscan=false;
> > >
> > > and then do the EXPLAIN ANALYSE for your select.
> >
> > I see, but that doesn't explain whether it is possible to speci
> "Owen Jacobson" <[EMAIL PROTECTED]> writes:
> > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> >> I see, but that doesn't explain whether it is possible to specify
the
> >> index to use. It seems that those options just force
> -Message d'origine-
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : mercredi, février 15, 2006 17:47
> À : Daniel Caune
> Cc : Andrew Sullivan; pgsql-sql@postgresql.org
> Objet : Re: [SQL] How to force PostgreSQL using an index
>
> "Daniel
Hi,
Is there any option to set so that psql provides the execution
time of each SQL statement executed?
Regards,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
> Hi,
>
> I need a special aggregation function. For instance, given the following
> table data:
>
>aid| cat | weight
> --+-+-
> a1 | Drama | 1
> a1 | Romance | 6
> a1 | Short | 1
> a1 | Other | 7
> a2 | Comedy | 1
> Objet : Re: [SQL] Ask a PostgreSql question (about select )
>
> Please post questions to the list. I'm forwarding this to the SQL
> list, as I think it's probably most applicable. I don't know much
> about the Oracle syntax you've used. Hopefully someone will be able
> to help you.
>
> On Mar 1
Hi,
I'm searching for an Oracle START WITH ... CONNECT BY PRIOR ...
equivalence. It seems that PostgreSQL (version >= 7.4) supports a
function connectby that provides similar feature. Unfortunately I don't
find any documentation on that function. Could you please give me a
link on such document
> -Message d'origine-
> De : Michael Fuhr [mailto:[EMAIL PROTECTED]
> Envoyé : lundi, mars 13, 2006 11:12
> À : Daniel Caune
> Cc : postgresql sql list
> Objet : Re: [SQL] connectby documentation
>
> On Mon, Mar 13, 2006 at 10:37:37AM -0500, Daniel Caune wr
> "outdated" packets is unfortunately a big issue on Debian. If you want
> to have up-to-date apt-packages try
>
> www.backports.org
>
> Add one of the mirrors from the list to your sources.list, then run
> apt-get update and then try to install again ...
> :-) And you'll see, that you can instal
> > Sorry, this is not my day: "apt-get install postgresql-contrib-8.1"
> > works fine... Just a link on the documentation that fully explains
how
> > connectby() works would be great! :-)
>
> The contrib package should have installed a file named
README.tablefunc.
>
You are right. The documen
> > > Sorry, this is not my day: "apt-get install
postgresql-contrib-8.1"
> > > works fine... Just a link on the documentation that fully
explains
> how
> > > connectby() works would be great! :-)
> >
> > The contrib package should have installed a file named
> README.tablefunc.
> >
>
> You are
> -Message d'origine-
> De : Michael Fuhr [mailto:[EMAIL PROTECTED]
> Envoyé : lundi, mars 13, 2006 19:26
> À : Daniel Caune
> Cc : [EMAIL PROTECTED]; postgresql sql list
> Objet : Re: [SQL] connectby documentation
>
> On Mon, Mar 13, 2006 at 06:38:14PM -05
> Hello,
>
> I have 2 tables where each table has a column named "comments" and the
> tables are related as a one to many. I want to concatenate all the
> comments of the many side to the one side so I wrote the following
> plpgsql function to do so.
>
>
> CREATE OR REPLACE FUNCTION fixcomment
OG: incomplete startup packet
Where can I check, please? Is it more likely a hardware problem (the
machine seems ok, no error detected)?
Regards,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
> I see you're running autovacuum. What's your disk subsytem look like?
By
> chance is it sitting on a RAID 5 that's running in degraded mode right
now
> while it scrubs?
>
Yes, that should be the problem. I will check that tomorrow morning
with a Linux administrator. Thanks.
> I have a simple table:
>
> name, url, counter
>
> I want to be able to do:
>
> SELECT * FROM table ORDER BY counter limit 5;
>
> But, I want counter to be incremented by 1 *if* the row is included in
> that 5 ... so that those 5 basically move to the bottom of the list, and
> the next 5 come
> BTW, I didn't complete my first thought above, which was to ask when
you
> last
> vacuumed the DB, but then I saw that you were running autovac, so that
> wasn't
> likely the problem.
>
> BTW, if the problem is actually a raid array that is rebuilding, it
should
> be
> (hopefullY) fixed by tomor
> > For example, the execution of the following query is fast as it used
to
> > be (gslog_event_id is the primary key on gslog_event):
> >
> > select max(gslog_event_id) from gslog_event; (=> Time: 0.773 ms)
> >
> >
> > while the following query is really slow (several minutes):
> >
> > select m
Hi,
How can I enter description for my custom types?
\dT provides information such as schema, name, and description for all
the registered types and custom types. I would like to provide a
description for each custom type I create.
Thanks,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090
each custom type I create.
> >
> > Thanks,
> >
> >
> > --
> > Daniel CAUNE
> > Ubisoft Online Technology
> > (514) 4090 2040 ext. 5418
> >
>
> Daniel,
>
> From the \h command in psql:
>
> rnd=# \h comment
> Command: COMMENT
&
,
b2 OUT int)
AS $$
V_b1 int;
V_b2 int;
BEGIN
FOR (...) LOOP
V_b1 = (...);
V_b2 = (...);
END LOOP;
b1 = V_b1;
b2 = V_b2;
END;
$$ LANGUAGE PLPGSQL;
Thanks,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
anks for the correction; I'm reading Daniel Caune's
notes
> on the docs now.
>
> -Owen
Funny! I started that thread on OUT parameter; that's a kind of
circle... :-)
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
---(end of broadca
Hi,
Is an index on a nullable column useful for retrieving rows having that
column null?
SELECT PlayerID
FROM PlayerLoginSession
WHERE EndTime IS NULL;
Regards,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
---(end of broadcast
> > Is an index on a nullable column useful for retrieving rows having that
> > column null?
>
> Nope, because IS NULL isn't an indexable operator.
>
> You can make an end-run around that with a partial index, eg
>
> create index fooi on foo(f1) where f1 is null
>
> This can be used to sa
rver 8.1.3
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418
---(end of broadcast)---
TIP 6: explain analyze is your friend
> De : Alvaro Herrera [mailto:[EMAIL PROTECTED]
>
> Daniel Caune wrote:
> > Hi,
> >
> > Is there any way to solve the following issue without dropping the
> > table?
> >
> > select count(*) from eventplayerleaveroom;
> > ERROR: could not a
> De : Alvaro Herrera [mailto:[EMAIL PROTECTED]
>
> Daniel Caune wrote:
> >
> > > De : Alvaro Herrera [mailto:[EMAIL PROTECTED]
> > >
> > > Daniel Caune wrote:
> > > >
> > > > select count(*) from eventplayerleaveroom;
&
> De : [EMAIL PROTECTED] [mailto:pgsql-sql-
> [EMAIL PROTECTED] De la part de Andrew Sullivan
>
> On Mon, Apr 24, 2006 at 12:17:07PM -0400, Daniel Caune wrote:
> > Hi,
> >
> > Is there any way to solve the following issue without dropping the
> > table?
>
> I need to write a function which inserts a log entry in a log table and
> only
> keeps the last 30 records. I was thinking of using a subquery similar to
> the
> following:
>
> insert into log (account_id, message) values (1, 'this is a test);
> delete from log where account_id = 1 and id not i
> >> insert into log (account_id, message) values (1, 'this is a test);
> >> delete from log where account_id = 1 and id not in ( select id from log
> >>where account_id = 1 order by timestamp desc limit 30);
> >>
> >> I'm wondering if there is a more performance oriented method of doing
> the
?
Thanks,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 490 2040 ext. 3613
Hi,
I would like to find an efficient solution for adding/implementing a constraint
UNIQUE on a VARCHAR column not case sensitive:
ALTER TABLE MyTable
ADD CONSTRAINT UNQ_MyTable_MyColumn
UNIQUE (lower(MyColumn)); -- invalid syntax
The idea is to have an index on that column, in a n
> -Message d'origine-
> De : Michael Glaesemann [mailto:[EMAIL PROTECTED]
> Envoyé : samedi 1 juillet 2006 10:01
> À : Daniel CAUNE
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Constraint UNIQUE on a column not case sensitive
>
>
> On Jul 1, 200
;
For instance, Ingres suggests prefixing local variable/parameter with
":" in the query:
UPDATE bar
SET i = :i;
I can continue using the de facto Oracle's naming convention
(P_parameter and V_local_variable) anyway. That's not a big deal.
Regards,
--
Daniel
earched for some similar cases in the pgsql-sql archive but nothing
really similar. Any idea?
Regards,
P.S.: I don't have this problem on other tables containing less data.
--
Daniel CAUNE
Ubisoft Online Technology
(514) 490 2040 ext. 3613
---(end of broadcast)-
,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 490 2040 ext. 3613
dy dropped the table, inserted data,
and tried to create all the indexes. The server systematically crashed when
creating some specific indexes. The only idea I have for the moment would
be to setup another machine with the same database environment. Other
idea(s)?
Thanks
--
Daniel CAUNE
Ubisoft Online Technology
(514) 490 2040 ext. 3613
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : jeudi, juillet 27, 2006 16:06
> À : Daniel Caune
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] PostgreSQL server terminated by signal 11
>
> "Daniel Caune" <[EMAIL PROTECTED]> writes:
> > My Post
> -Message d'origine-
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : jeudi, juillet 27, 2006 16:06
> À : Daniel Caune
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] PostgreSQL server terminated by signal 11
>
> "Daniel Caune" <[E
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> De la part de Tom Lane
> Envoyé : jeudi 27 juillet 2006 19:26
> À : Daniel Caune
> Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org
> Objet : Re: [SQL] PostgreSQL server terminated
> -Message d'origine-
> De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> De la part de D'Arcy J.M. Cain
> Envoyé : jeudi 27 juillet 2006 19:49
> À : Daniel Caune
> Cc : [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql-
> [EMAIL PROTECTED]
> O
> -Message d'origine-
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : jeudi, juillet 27, 2006 19:26
> À : Daniel Caune
> Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org
> Objet : Re: [SQL] PostgreSQL server terminated by signal 11
>
> &qu
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : vendredi, juillet 28, 2006 09:38
> À : Daniel Caune
> Cc : pgsql-admin@postgresql.org; pgsql-sql@postgresql.org
> Objet : Re: [SQL] PostgreSQL server terminated by signal 11
>
> "Daniel Caune" <[EMAIL PROTEC
Hi,
Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92
compliant?
PostgreSQL requires this keyword by default when defining alias, which might
be good thing. I mean, I would prefer being notified from a syntax error
than spending a couple of hours wondering why a client a
Hi,
How does the IF statement evaluate conditions? Does it evaluate conditions
following their declaration order from left to right? In case of
or-conditions, does the IF statement stop evaluating conditions whenever a
first or-condition is true?
The following snippet seems to be invalid, which
> hi,
>
> am migrating a database from MSSQL to postgres. How would i migrate
> this:
>
> [Id] [numerc](18, 0) IDENTITY (1, 1)
>
You might want to create a sequence first, such as with more or less
options:
CREATE SEQUENCE my_sequence
INCREMENT BY 1
MINVALUE 1
NO MAXVALUE
START WITH 1
> I believe:
> IDENTITY(1, 1) just means "Primary Key" in M$SQL
>
IDENTITY is not obligatory a primary key. It's an auto-incremented column.
It might correspond to a PostgreSQL sequence.
[http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts
_ia-iz_3iex.asp]
> numeric 18,
> De : [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
> De la part de Kenneth Gonsalves
> Envoyé : 15 octobre 2006 08:31
> À : pgsql-sql@postgresql.org
> Objet : [SQL] conversion of numeric field from MSSQL to postgres
>
> hi,
>
> am migrating a database from MSSQL to postgres. How would i migrate
>
Hi,
I have read in the future PostgreSQL 8.2 release note
(http://developer.postgresql.org/pgdocs/postgres/release-8-2.html) that
DELETE RETURNING will be supported. Tom, could please confirm that the
following PL/PGSQL statements will work on PostgreSQL 8.2?
INSERT INTO a(x, y)
DELETE FRO
> I tried to do the following in PostgreSQL:
>
> DECLARE
>v_version VARCHAR;
>
> BEGIN
>SELECT version INTO v_version FROM version WHERE id = 1;
>
>IF v_version <> ''1.0.0.0'' THEN
> RAISE EXCEPTION ''This script needs Agenda version 1.0.0.0,
> detected version %'', v_version;
>
Hi,
I was wondering when it is better to choose sequence, and when it is better
to use serial. The serial type is a sequence with default parameters
(http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SE
RIAL). Actually, I never use serial - I prefer sequence for some re
> I'm informed that the last statement of a function that returns void
> cannot be
> a SELECT. How else is one supposed to call another function which
also
> returns void?
>
PERFORM
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
> PERFORM works only in plpgsql, Peter wrote a pl/sql-function...
>
Oups, sorry! I missed the point.
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Hi,
We are using Erlang, PostgreSQL 8.2.5, and the Erlang library pgsql
developed by Christian Sunesson
(http://jungerl.cvs.sourceforge.net/jungerl/jungerl/lib/pgsql/).
The Erlang library pgsql is easy to use and does the job so far, at
least for our prototyping phase. However, this library has
Hi,
I'm facing a strange behaviour with a statement SELECT ... LIMIT n FOR
UPDATE in PostgreSQL 8.1. The number of rows returned is actually (n -
1). I'm trying to find whether this is an identified issue with
PostgreSQL 8.1 that might have been fixed in a later version such as
8.2; I don't have
> -Message d'origine-
> De : Tom Lane [mailto:[EMAIL PROTECTED]
> Envoyé : mardi, novembre 27, 2007 23:46
> À : Daniel Caune
> Cc : pgsql-sql@postgresql.org
> Objet : Re: [SQL] Strang behaviour SELECT ... LIMIT n FOR UPDATE
>
> "Daniel Caune" <[
> De : Tom Lane [mailto:[EMAIL PROTECTED]
>
> "Daniel Caune" <[EMAIL PROTECTED]> writes:
> > I did the following test, removing all the where-clause from the SELECT
> statement. Every statement completes immediately, i.e. it doesn't block.
>
> I
Hi,
I tried to figure out how to select a particular schema before executing
statement on its objects without having to specify the schema in these
statements (cf. the instruction USE supported in some other RDBMS)
PostgreSQL allows modifying dynamically the schema search path:
SET search_path
Hi,
Is there any way to define a SQL stored function that inserts a row in a
table and returns the serial generated?
CREATE TABLE matchmaking_session
(
session_id bigint NOT NULL DEFAULT
nextval('seq_matchmaking_session_id'),
...
);
CREATE FUNCTION create_matchmaking_sesssion(...)
RETURNS
> What about
> $$
> INSERT INTO ;
> select currval('seq_matchmaking_session_id');
> $$ language sql;
>
> ?
Indeed... :-( For some reason, I thought that it was not possible to
have to SQL statement in an SQL stored function.
By the way, is there any performance difference between pure SQL a
Hi,
Could you please tell me what does mod - in "mod statement" - stand for?
"log_statement (string)
Controls which SQL statements are logged. Valid values are none, ddl,
mod, and all. (...). mod logs all ddl statements, plus data-modifying
statements such as INSERT, UPDATE, DELETE, TRUNCATE, an
76 matches
Mail list logo
