On Mon, Aug 26, 2013 at 3:17 AM, gajendra s v wrote:
> Please explain me why it is ?
>
A good place to start would be removing all the parts here that don't seem
to matter. Your problem seems to be with the recursive query (since that is
the part you're changing). Cut off everything else and com
Hello All,
I am migrating oracle queries to postgres queries
*Oracle query is below*
select * from (select * from KM_COURSE_MAST where ID in (select OBJ_ID from
(select OBJ_ID,PERFORMER_TYPE,PERFORMER_ID from KM_REL_OBJ_PER_ACTION
where OBJ_TYPE='COURSETYPE') where PERFORMER_TYPE='GROUP'
2012/4/3 Alban Hertroys
> On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
>
> > That is right, there is no sense to use cursors here...
>
> I think you're wrong there: The OP is querying a system table for tables
> of a certain name, which I expect can contain multiple rows for tables of
> the sa
On 2 Apr 2012, at 22:02, leaf_yxj wrote:
> CREATE OR REPLACE FUNCTION truncate_t(tablename IN VARCHAR) RETURNS void AS
> $$
> DECLARE
> stmt RECORD;
> statements CURSOR FOR SELECT tablename FROM pg_catalog.pg_tables;
> BEGIN
> IF stmt IN statements then
> EXECUTE 'TRUNCAT
On 2 Apr 2012, at 22:28, Bartosz Dmytrak wrote:
> That is right, there is no sense to use cursors here...
I think you're wrong there: The OP is querying a system table for tables of a
certain name, which I expect can contain multiple rows for tables of the same
name in different schema's.
Of c
That is right, there is no sense to use cursors here...
CREATE OR REPLACE FUNCTION truncate_t (IN tablename text)
RETURNS VOID
AS
$$
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(tablename) || 'CASCADE;';
EXCEPTION
WHEN undefined_table THEN
RAISE EXCEPTION 'Table "%" does not exists', tablename
Hello
" IF stmt IN statements then " is nonsense.
use trapping exceptions instead
BEGIN
EXECUTE 'TRUNCATE TABLE ' || quote_ident(_tablename) || ' CASCADE';
EXCEPTION WHEN undefined_table THEN
RAISE EXCEPTION 'your own exception, when you like';
END;
Regards
Pavel
2012/4/2 leaf_yxj :
> I
I tried to create function to truncate table
1) when the user call the function just specify the tablename
2) the user can use the function owner privilege to execute the function.
But I got the errors as follows. Please help me to take a look.
Thanks.
Regards.
Grace
-- function :
CREA
In response to peeratat tungsungnern :
>
> My HDD has bad sector and i can change new HDD. I have a ploblem can't back
> up database and display massage is:
>
> pg_dump: SQL command failed
> pg_dump: Error message from server: ERROR: cache lookup failed for function
> 137832813
> pg_dump: The
My HDD has bad sector and i can change new HDD. I have a ploblem can't back up
database and display massage is:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: cache lookup failed for function
137832813
pg_dump: The command was: SELECT tableoid, oid, adnum,
pg_cat
Nikolas Everett wrote:
> Sorry. Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
> NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
> '12 hours');
> INSERT INTO test (state1, state2, tim
Got it:
SELECT state1, timestamp
FROM (SELECT state1, timestamp, lag(state1) OVER (ORDER BY timestamp)
FROM test) as foo
WHERE state1 != lag OR lag IS NULL
ORDER BY timestamp;
state1 | timestamp
+
1 | now() - interval '12 hours'
The 10 and 11 hour interval are being skipped because I'm only interested in
the transitions of state 1. State 1 only transitioned three times at now -
12, now - 9 and now - 8.
The table has both transitions in it because I frequently care about them
both together. I just don't in this case.
On
On 5/27/2010 9:45 AM, Nikolas Everett wrote:
> Sorry. Here is the setup:
> CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL,
> state2 INT NOT NULL, timestamp TIMESTAMP);
> INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() -
> interval '12 hours');
> INSERT INTO tes
On 5/27/2010 9:04 AM, Nikolas Everett wrote:
> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
>
> I'm trying to write a query that coalesces changes in state2 away to
> produce just a list of transitions of state1. I guess it
Sorry. Here is the setup:
CREATE TABLE test (id BIGSERIAL PRIMARY KEY, state1 INT NOT NULL, state2 INT
NOT NULL, timestamp TIMESTAMP);
INSERT INTO test (state1, state2, timestamp) VALUES (1, 1, now() - interval
'12 hours');
INSERT INTO test (state1, state2, timestamp) VALUES (1, 2, now() - interva
Lost me a bit, do you mean DISTINCT?
select distinct state1, first(timestamp) from table
On May 27, 2010, at 10:04 AM, Nikolas Everett wrote:
> Say I have a table that stores state transitions over time like so:
> id, transitionable_id, state1, state2, timestamp
>
> I'm trying to write a qu
Say I have a table that stores state transitions over time like so:
id, transitionable_id, state1, state2, timestamp
I'm trying to write a query that coalesces changes in state2 away to produce
just a list of transitions of state1. I guess it would look something like
SELECT state1, FIRST(timest
Tom Lane wrote:
pgsql writes:
Tom Lane wrote:
Um, that's not too helpful, we want to see the string it's pointing at.
Sorry about that. All statements are calling one of two pl/pgsql
functions. While that information already helps me a lot, it'll take me
a while to step through the code. T
pgsql writes:
> Tom Lane wrote:
>> Um, that's not too helpful, we want to see the string it's pointing at.
> Sorry about that. All statements are calling one of two pl/pgsql
> functions. While that information already helps me a lot, it'll take me
> a while to step through the code. Those funct
Tom Lane wrote:
pgsql writes:
Tom Lane wrote:
Looking at debug_query_string in the core dumps would
at least show what SQL command is calling the function(s) --- and I
wouldn't be surprised if there's exactly one function involved here.
Content of debug_query_string:
core.21207
$1 = 6310
pgsql writes:
> Tom Lane wrote:
>> Looking at debug_query_string in the core dumps would
>> at least show what SQL command is calling the function(s) --- and I
>> wouldn't be surprised if there's exactly one function involved here.
> Content of debug_query_string:
> core.21207
> $1 = 63106368
U
Tom Lane wrote:
pgsql writes:
Looking at debug_query_string in the core dumps would
at least show what SQL command is calling the function(s) --- and I
wouldn't be surprised if there's exactly one function involved here.
Content of debug_query_string:
core.21207
$1 = 63106368
core.20832
$1 =
Alvaro Herrera wrote:
pgsql wrote:
Hi,
one of our pgsql instances recently started to segfault multiple times a
week. I tried a couple of things to pin it down to a certain query
or job but failed to find any pattern. All I can offer is some notes
and a set of similar looking back traces.
Ple
On 5/05/2010 5:27 AM, Alvaro Herrera wrote:
pgsql wrote:
Hi,
one of our pgsql instances recently started to segfault multiple times a
week. I tried a couple of things to pin it down to a certain query
or job but failed to find any pattern. All I can offer is some notes
and a set of similar look
pgsql writes:
> one of our pgsql instances recently started to segfault multiple times a
> week. I tried a couple of things to pin it down to a certain query
> or job but failed to find any pattern. All I can offer is some notes
> and a set of similar looking back traces.
BTW, there is a post-8.3
pgsql writes:
> one of our pgsql instances recently started to segfault multiple times a
> week. I tried a couple of things to pin it down to a certain query
> or job but failed to find any pattern. All I can offer is some notes
> and a set of similar looking back traces.
All of those traces seem
pgsql wrote:
> Hi,
>
> one of our pgsql instances recently started to segfault multiple times a
> week. I tried a couple of things to pin it down to a certain query
> or job but failed to find any pattern. All I can offer is some notes
> and a set of similar looking back traces.
Please install th
Hi,
one of our pgsql instances recently started to segfault multiple times a
week. I tried a couple of things to pin it down to a certain query
or job but failed to find any pattern. All I can offer is some notes
and a set of similar looking back traces.
Thanks in advance.
Machine details
---
On Apr 21, 2005, at 1:22 AM, amit kumar awasthi wrote:
# Using sockets credentials for improved security. Not available
everywhere,
# but works on Linux, *BSD (and probably some others)
local all all ident sameuser
#local all all 127.0.0.1 sameuser
only this en
Hello sir
actually i am working with Qt using postgresql i have to get connection
{(database("trainee"),user("trainee"))}
from the network (database remote accessing) so i have configured
postgresql.conf and pg_hba.conf upto some extents
the fields that i have configured r as follows even t
Bo Berkhaut writes:
> CREATE TABLE example (name text, slots text[]);
>
> I want to be able to select, for example, all rows such that an element
> of example.slots exists equal to example.name.
There are some operators for this in contrib/array.
However, I'd say that if you're trying to do thi
Hi!
Would you please explain me how can I express in PostgreSQL the notion of
"any element of the array". Having a table like:
CREATE TABLE example (name text, slots text[]);
I want to be able to select, for example, all rows such that an element
of example.slots exists equal to example.name.
So
> > Please help me recover our database from what I think
> > is a duplicate key in unique index problem.
>
> This may not help, and forgive my asking the obvious, but have
> you done a SELECT on the table and actually *seen* duplicate 'id'
> values in the SERIAL column? That would surprise me be
34 matches
Mail list logo
