Re: [SQL] Number timestamped rows

[Jasen Betts]

On 2011-11-02, Jan Peters  wrote:
> Dear all,
> maybe a stupid question, but: I have a table that is ordered like this:
>

Tables aren't ordered.  Sometimes they may seem to be ordered, 
but they seldom stay that way for long.

> and I would like to number them according to their timestamps like this:

> How would I do this with an UPDATE statement (e.g.) in pgsql?

If you want ordered data use an ORDER BY clause in the select,
there is no other reliable way.


-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] the use of $$string$$

[Jasen Betts]

On 2011-11-07, Richard Huxton  wrote:
> On 05/11/11 00:12, John Fabiani wrote:

> OK, so it seems psycopg is quoting your strings for you (as you'd 
> expect). It's presumably turning your query into:
>  ... values (E'123', $$E''$$)
> So - the $$ quoting is unnecessary here - just use the % placeholders.
>
> Incidentally, should it be %s for the numeric argument?

psycopg2 seems to only accept %s as a place-holder, it's not printf it
just looks a bit like it.

-- 
⚂⚃ 100% natural


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] GROUP and ORDER BY

[Tarlika Elisabeth Schmitz]

On Tue, 08 Nov 2011 09:57:08 +0530
Robins Tharakan  wrote:

>On 11/08/2011 02:50 AM, Tarlika Elisabeth Schmitz wrote:
>> Hello,
>>
>> I would like to GROUP the result by one column and ORDER it by
>> another:
>>
>> SELECT
>>  no, name, similarity(name, 'Tooneyvara') AS s
>>  FROM vtown
>>  WHEREsimilarity(name, 'Tooneyvara')>  0.4
>>  ORDER BY s DESC
>>
>> Result:
>>
>> 1787 "Toomyvara" 0.5
>> 1787 "Toomevara" 0.4
>> 1188 "Toonybara" 0.4
>>
>>
>> Desired result:
>>
>> 1787 "Toomyvara" 0.5
>> 1188 "Toonybara" 0.4
>>
>> Gets rid of the  duplicate "no" keeping the spelling with the greater
>> similarity and presents the remaining result ordered by similarity.
>>
>>[...]

>[...] does this work ?
>
>SELECT no, name, MAX(similarity(name, 'Tooneyvara')) AS sim
>FROM vtown
>WHERE similarity(name, 'Tooneyvara') > 0.4
>GROUP BY no, name
>ORDER BY sim DESC
>
>--
>Robins Tharakan

Thank you for yuor suggestion, Robins. Unfortunately, it does not work;
this returns:
1787"Toomyvara" 0.5
1787"Toomevara" 0.4
1188"Toonybara" 0.4
because while column "no" is identical, "name" isn't and you're
grouping by both of them.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Issue with a variable in a function

[tlund79]

I've created a function which purpose is to import data to an excel report.
This is however the first time I'm doing this, and I've exhausted all other
options before asking the question here.

I call this function with this command: select ppr_data(2011,1,52,8)

The issue relates to the variable "prosjektkode" ($4). When this is a
singular digit the function runs as expected and the data appears correctly
in the report. The issue appears when "prosjektkode" is multiple digits,
i.e. 8,3,119 (i.e. I want run a report on multiple "prosjektkode"), when I
do this it fails. I've tried to declare this variable as text and tried
escaping the commas, but no luck.

The function:

CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int) RETURNS int AS $$
DECLARE 
antall bigint;

BEGIN

--Henter Inngang Antall Kunder
select count(distinct a.kundenr) into antall

from aktivitet a
inner join utgave u on u.utgaveid=a.utgaveid
inner join prosjekt p on p.prosjektkode=u.prosjektkode

where a.utfort=1
and a.aktivtypekode in (82,83)
and extract(year from a.utforesdato) = $1 -- Aarstall
and extract(week from a.utforesdato) >= $2 -- Fra_uke
and extract(week from a.utforesdato) <= $3 -- Til_uke
and p.prosjektkode in ($4)


and a.kundenr in (

select o.kundenr

from ordrer o
inner join utgave u on u.utgaveid=o.utgaveid
inner join prosjekt p on p.prosjektkode=u.prosjektkode


where o.ordretypenr in (1, 3, 4, 5) /* utelater ordretypen kredittordre */
and o.kreditert is null /* utelater krediterte ordre */
and o.ordrestatus in (3, 4) /* kun ordrer med status fakturert og klar til
fakturert */
and o.ordresum > 0 /* Utelater 0-ordre og f.eks. messeeksemplar */
and extract(year from o.ordredato) = ($1 - 1)
and p.prosjektkode in ($4)
);

RETURN antall;
END;
$$ LANGUAGE plpgsql;


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4974235.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] GROUP and ORDER BY

[Robins Tharakan]

Hmmm... Missed that!

I think you are looking for the feature that was introduced in 
PostgreSQL 9.1 where you could have a non-group-by column in the select 
list, but only if the group-by has a pkey to identify the actual row.


http://www.postgresql.org/docs/9.1/static/release-9-1.html
(Search for GROUP BY)

--
Robins Tharakan

On 11/08/2011 03:29 PM, Tarlika Elisabeth Schmitz wrote:

Thank you for yuor suggestion, Robins. Unfortunately, it does not work;
this returns:
1787"Toomyvara"   0.5
1787"Toomevara"   0.4
1188"Toonybara"   0.4
because while column "no" is identical, "name" isn't and you're
grouping by both of them.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Partitionning + Trigger and Execute not working as expected

[Sylvain Mougenot]

Hello,
I'm trying to use table partitionning on a table called JOB.
Each month a new table is created to contain the rows created on that month.
ex : JOB_2011_11 for rows created during november 2011.

To do that I followed this advices on that page :
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
I also would like to create code dynamically into the trigger in order to
have all "INSERT INTO" inheritated tables (tables like JOB__MM) queries
done.

But I can't make it work. I've an error when the insert is done using
EXECUTE.
*Working :* INSERT INTO job_2011_11 values (NEW.*);
*Not Woking : *EXECUTE 'INSERT INTO '|| currentTableName || ' values
(NEW.*)';

Could someone tell me how to make this EXECUTE work?
Thank you
Sylvain

Bellow is the full code (trigger) and error.
*Code:*
CREATE OR REPLACE FUNCTION job_insert_trigger()
  RETURNS trigger AS
$BODY$
DECLARE
currentTableName character varying := 'job_'
||to_char(NEW.datecreation,'_MM');
BEGIN
IF (NOT check_exist_table(currentTableName)) THEN
PERFORM add_table_job__mm(currentTableName, NEW.datecreation);
END IF;

EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';
RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

*Error:*
ERREUR: missing FROM clause for table « new »
SQL :42P01

Re: [SQL] Issue with a variable in a function

[David Johnston]

-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of tlund79
Sent: Tuesday, November 08, 2011 8:17 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Issue with a variable in a function


The issue relates to the variable "prosjektkode" ($4). 

CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int) RETURNS int AS $$ DECLARE antall bigint;

---/Original Message--

Read about "ARRAY"s

Change your function signature to something like:

CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int[] )  -- Note the change to int[] from int for prosjektkode

David J.



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Updatable view should truncate table fields

[Russell Keane]

Using PostgreSQL 9.0.

We have a table which is not accessible by client code.
We also have views with rules and triggers to intercept any insert or update 
statements and write that data in a slightly different format back to the table.

A particular field in the table is currently 5 chars but recently we have had 
update / insert statements containing more than 5.
This obviously (and correctly) throws an error.

We can extend the table to accept more than 5 characters but the view must 
return 5 characters.
If we try to extend the table to accept, say, 10 characters the view will 
display 10.
If I also cast the view field to 5 characters then any insert with more than 5 
characters still fails.

Any ideas???






Create table blah_table
(
blah_id int,
fixed_field char(5)
);

Create or replace view blah_view as
Select
blah_id,
fixed_field
from blah_table;

CREATE OR REPLACE FUNCTION process_blah_insert(blah_view) RETURNS void AS $body$
Begin

Insert into blah_table
(
blah_id,
fixed_field
)
Select
$1.blah_id,
$1.fixed_field
;
End;
$body$ language plpgsql;

CREATE OR REPLACE FUNCTION process_blah_update(blah_view) RETURNS void AS $body$
Begin

Update blah_table
Set
fixed_field = $1.fixed_field
where
blah_id = $1.blah_id
;
End;
$body$ language plpgsql;


create or replace rule blah__rule_ins as on insert to blah_view
do instead
SELECT process_blah_insert(NEW);

create or replace rule blah__rule_upd as on update to blah_view
do instead
SELECT
process_blah_update(NEW);


insert into blah_view values (1, '12345');
insert into blah_view values (2, '123456'); --This line fails obviously







Regards,


Russell Keane

INPS

Subscribe to the Vision e-newsletter
Subscribe to the Helpline Support 
Bulletin
[cid:image003.png@01CC9E5E.26083BD0]  Subscribe to the Helpline Blog RSS 
Feed



Registered name: In Practice Systems Ltd.
Registered address: The Bread Factory, 1a Broughton Street, London, SW8 3QJ
Registered Number: 1788577
Registered in England
Visit our Internet Web site at www.inps.co.uk
The information in this internet email is confidential and is intended solely 
for the addressee. Access, copying or re-use of information in it by anyone 
else is not authorised. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of INPS or any of its affiliates. 
If you are not the intended recipient please contact is.helpd...@inps.co.uk

<>

Re: [SQL] Updatable view should truncate table fields

[David Johnston]

From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org]
On Behalf Of Russell Keane
Sent: Tuesday, November 08, 2011 4:34 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Updatable view should truncate table fields

Using PostgreSQL 9.0.

We have a table which is not accessible by client code.
We also have views with rules and triggers to intercept any insert or update
statements and write that data in a slightly different format back to the
table.

A particular field in the table is currently 5 chars but recently we have
had update / insert statements containing more than 5.
This obviously (and correctly) throws an error.

We can extend the table to accept more than 5 characters but the view must
return 5 characters.
If we try to extend the table to accept, say, 10 characters the view will
display 10.
If I also cast the view field to 5 characters then any insert with more than
5 characters still fails.

-

Haven't used updatable VIEWs yet but couldn't you either define the VIEW as:

CREATE VIEW  AS (
SELECT  field1, field2::varchar(5) AS field2
FROM table
);

Or, alternatively, define the INSERT/UPDATE functions to perform the
truncation upon inserting into the table?

Does the INSERT itself throw the error or is one of your functions raising
the error when it goes to insert the supplied value into the table?

It is generally bad form to modify user data for storage so either  you
truly have a length limitation that you need to restrict upon data entry (in
which case everything is working as expected) or you should allow any length
of data to be input and, in cases where the output medium has length
restrictions, you can ad-hoc limit the display length of whatever data was
provided.

David J.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] help with xpath namespace

[Ross Reedstrom]

On Mon, Sep 26, 2011 at 09:56:06AM -0400, Brian Sherwood wrote:
> Yep, that did it.
> 
> Thanks!

ProTip (for the list archive): since the namespace alias in the query and the
original XML don't need to match (in this common case, the document uses a
default namespace, which isn't available in xpaths), you can save significant
typing by using a single-character namespace:

select xpath(
    '/j:chassis-inventory/j:chassis/j:serial-number/text()',
    data_xml,
    ARRAY[ARRAY['j',
'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
)
from xml_test;

Ross

> 
> 
> 2011/9/23 Filip Rembiałkowski :
> >
> >
> > 2011/9/22 Brian Sherwood 
> >>
> >> select (xpath('/chassis-inventory/chassis/serial-number/text()',
> >>        data_xml,
> >>        ARRAY[ARRAY['junos',
> >> 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
> >>       )) from xml_test;
> >>
> >> Can anyone suggest how I would go about getting the serial-number with
> >> xpath?
> >>
> >
> >
> > http://www.postgresql.org/docs/9.1/static/functions-xml.html#FUNCTIONS-XML-PROCESSING
> > - see "mydefns".
> >
> > This will work:
> >
> > select xpath(
> >     '/junos:chassis-inventory/junos:chassis/junos:serial-number/text()',
> >     data_xml,
> >     ARRAY[ARRAY['junos',
> > 'http://xml.juniper.net/junos/9.6R4/junos-chassis']]
> > )
> > from xml_test;
> >
> >
> >
> > cheers, Filip
> >
> >
> >
> 
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Partitionning + Trigger and Execute not working as expected

[Josh Kupershmidt]

On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot  wrote:
> EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)';

The quotes in the above line are wrong; you want it like:

EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*);

Josh

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql