[SQL] trigger parameters, what am I doing wrong ??

[Marcin Krawczyk]

Hi guys. I'm trying to pass a parameter to trigger procedure but it's not
working and I have no idea why since it looks pretty ok. Here's what I do :

CREATE OR REPLACE FUNCTION test_proc()
  RETURNS "trigger" AS
$BODY$
DECLARE
chk boolean;
parinteger := TG_ARGV[0];

BEGIN
RAISE NOTICE 'TG_ARGV = %, TG_NARGS = %, par = %', TG_ARGV[0], TG_NARGS,
par;

-- [...] some computations

RETURN NEW;
END;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER jks_test_proc_tg
  AFTER UPDATE
  ON test_table
  FOR EACH ROW
  EXECUTE PROCEDURE test_proc(42);

And here's what RAISE NOTICE looks like : NOTICE:  TG_ARGV = ,
TG_NARGS = 0, par = 

What's wrong with it ?? I'm running 8.1.4

regards
mk

Re: [SQL] 100% CPU at concurent access

[Sabin Coanda]

Hi Tom,

Well, I thought the connection with the failed transaction checks in a loop 
until the succeeded transaction will finish, and then it will succeeded as 
well.

However, would you suggest me a code for "TestProcOuter" that works and 
fulfils my desire, please ? :)


Thanx,
Sabin 



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

[SQL] sequence number in a result

[Campbell, Lance]

Say I have the following SQL statement:

 

SELECT a, b, c FROM t1 ORDER BY a;

 

Is there a function or special system label I can use that would
generate a sequence number in the returning result set?

 

Example:

SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a;

 

Result:

ab c   order

-

Aa  bb  cc  1

A1  bb  cc  2

A2  bb  cc  3

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu  

 

Re: [SQL] sequence number in a result

[Oliveiros Cristina]

Howdy, Lance.

I had that problem about a year ago, and AFAIK there is no solution, at least 
not in SQL Standard.

What I did was something like

SELECT a,b,c,count(y.a) as order
FROM t1 x , t1 y
WHERE  ((x.a >  y.a)
OR (x.a = y.a 
AND x.ID <= y.ID))   -- Use here whatever you have as primary key on your 
table...
GROUP BY x.a,x.b,x.c ;
ORDER BY a ;

But this trick is just for relatively small tables.
When I needed something for bigger tables, I did it programmatically

But, maybe PostGreSQL has some proprietary function which I dunno that can do 
precisely this...

HTH a little...

Best,
Oliveiros 
"(SELECT 
resumo1.\"iPages\",resumo1.\"iUniqueVisitors\",resumo1.\"IDSite\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\",
 " +

"COUNT(resumo2.\"iPages\") as rank " +

"FROM " + m_strSUBQUERY_INTERFACE + " resumo1," +

" " + m_strSUBQUERY_INTERFACE + " resumo2 " +

"WHERE ((resumo1.\"dtDate\" = @diadehoje) " +

"AND (resumo2.\"dtDate\" = @diadehoje)) " +

"AND ((resumo1.\"iPages\" < resumo2.\"iPages\" ) " +

"OR (resumo1.\"iPages\" = resumo2.\"iPages\" " +

"AND resumo1.\"IDSiteResume\" <= resumo2.\"IDSiteResume\")) " + // Tinha e tem 
IDSiteResume 

"GROUP BY resumo1.\"iPages\",resumo1.\"iUniqueVisitors\"," +

"resumo1.\"IDSite\",resumo1.\"dtDate\",resumo1.\"IDSiteResume\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\"
 


  - Original Message - 
  From: Campbell, Lance 
  To: pgsql-sql@postgresql.org 
  Sent: Thursday, October 09, 2008 5:31 PM
  Subject: [SQL] sequence number in a result 


  Say I have the following SQL statement:

   

  SELECT a, b, c FROM t1 ORDER BY a;

   

  Is there a function or special system label I can use that would generate a 
sequence number in the returning result set?

   

  Example:

  SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a;

   

  Result:

  ab c   order

  -

  Aa  bb  cc  1

  A1  bb  cc  2

  A2  bb  cc  3

   

  Thanks,

   

  Lance Campbell

  Project Manager/Software Architect

  Web Services at Public Affairs

  University of Illinois

  217.333.0382

  http://webservices.uiuc.edu

   

Re: [SQL] sequence number in a result

[Oliveiros Cristina]

Sorry, Lance
By mistake I posted my own things, which I was using to "adapt" to your 
situation. :-)
Please kindly ignore everything below my signature :p

Best,
Oliveiros
  - Original Message - 
  From: Oliveiros Cristina 
  To: Campbell, Lance ; pgsql-sql@postgresql.org 
  Sent: Thursday, October 09, 2008 5:48 PM
  Subject: Re: [SQL] sequence number in a result 


  Howdy, Lance.

  I had that problem about a year ago, and AFAIK there is no solution, at least 
not in SQL Standard.

  What I did was something like

  SELECT a,b,c,count(y.a) as order
  FROM t1 x , t1 y
  WHERE  ((x.a >  y.a)
  OR (x.a = y.a 
  AND x.ID <= y.ID))   -- Use here whatever you have as primary key on your 
table...
  GROUP BY x.a,x.b,x.c ;
  ORDER BY a ;

  But this trick is just for relatively small tables.
  When I needed something for bigger tables, I did it programmatically

  But, maybe PostGreSQL has some proprietary function which I dunno that can do 
precisely this...

  HTH a little...

  Best,
  Oliveiros 
  "(SELECT 
resumo1.\"iPages\",resumo1.\"iUniqueVisitors\",resumo1.\"IDSite\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\",
 " +

  "COUNT(resumo2.\"iPages\") as rank " +

  "FROM " + m_strSUBQUERY_INTERFACE + " resumo1," +

  " " + m_strSUBQUERY_INTERFACE + " resumo2 " +

  "WHERE ((resumo1.\"dtDate\" = @diadehoje) " +

  "AND (resumo2.\"dtDate\" = @diadehoje)) " +

  "AND ((resumo1.\"iPages\" < resumo2.\"iPages\" ) " +

  "OR (resumo1.\"iPages\" = resumo2.\"iPages\" " +

  "AND resumo1.\"IDSiteResume\" <= resumo2.\"IDSiteResume\")) " + // Tinha e 
tem IDSiteResume 

  "GROUP BY resumo1.\"iPages\",resumo1.\"iUniqueVisitors\"," +

  
"resumo1.\"IDSite\",resumo1.\"dtDate\",resumo1.\"IDSiteResume\",resumo1.\"IDWeboramaAccount\",resumo1.\"Visits\"
 


- Original Message - 
From: Campbell, Lance 
To: pgsql-sql@postgresql.org 
Sent: Thursday, October 09, 2008 5:31 PM
Subject: [SQL] sequence number in a result 


Say I have the following SQL statement:

 

SELECT a, b, c FROM t1 ORDER BY a;

 

Is there a function or special system label I can use that would generate a 
sequence number in the returning result set?

 

Example:

SELECT a, b, c, SOMELABEL as order FROM t1 ORDER BY a;

 

Result:

ab c   order

-

Aa  bb  cc  1

A1  bb  cc  2

A2  bb  cc  3

 

Thanks,

 

Lance Campbell

Project Manager/Software Architect

Web Services at Public Affairs

University of Illinois

217.333.0382

http://webservices.uiuc.edu

 

Re: [SQL] sequence number in a result

[Tom Lane]

"Campbell, Lance" <[EMAIL PROTECTED]> writes:
> Is there a function or special system label I can use that would
> generate a sequence number in the returning result set?

The usual hack is a temporary sequence:

regression=# create temp sequence s1;
CREATE SEQUENCE
regression=# select nextval('s1'), * from (select * from int8_tbl order by q1) 
ss;
 nextval |q1|q2 
-+--+---
   1 |  123 |   456
   2 |  123 |  4567890123456789
   3 | 4567890123456789 |   123
   4 | 4567890123456789 |  4567890123456789
   5 | 4567890123456789 | -4567890123456789
(5 rows)

Note that you must use a subselect to ensure that the sequence number
gets stuck on *after* the ORDER BY happens, else what you'll probably
get is numbering corresponding to the unsorted row order.

It would be possible to write a C function to do this with a lot less
overhead than a sequence entails, but no one's got round to it AFAIK.

regards, tom lane

-- 
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] trigger parameters, what am I doing wrong ??

[Tom Lane]

"Marcin Krawczyk" <[EMAIL PROTECTED]> writes:
> And here's what RAISE NOTICE looks like : NOTICE:  TG_ARGV = ,
> TG_NARGS = 0, par = 
> What's wrong with it ?? I'm running 8.1.4

Works for me:

regression=# insert into test_table values(1);
INSERT 0 1
regression=# update test_table set f1 = 2;
NOTICE:  TG_ARGV = 42, TG_NARGS = 1, par = 42
UPDATE 1

You need to show a more complete example of what you're doing.

regards, tom lane

-- 
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] sequence number in a result

[Relyea, Mike]

>  Is there a function or special system label I can use that would
generate a sequence number in the returning result set?  


Would something like this work for you?

CREATE TEMP SEQUENCE foo;
SELECT a, b, c, nextval('foo') AS order FROM t1 ORDER BY a;

Mike Relyea
Product Development Engineer
Xerox Corporation
Building 218 Room 107
800 Phillips Rd
Webster, NY 14580

p 585.265.7321

-- 
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] many-to-many relationship

[Steve Midgley]

At 05:20 PM 10/8/2008, [EMAIL PROTECTED] wrote:

Date: Wed, 8 Oct 2008 11:25:10 +0200
From: Louis-David Mitterrand <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: Re: many-to-many relationship
Message-ID: <[EMAIL PROTECTED]>
Mail-Followup-To: pgsql-sql@postgresql.org
References: <[EMAIL PROTECTED]> 
<[EMAIL PROTECTED]>

In-Reply-To: <[EMAIL PROTECTED]>
X-Archive-Number: 200810/23
X-Sequence-Number: 31665

>
> |id|image_url|f_table|f_key
> |1 |url..|person |1234
> |2 |url2.|event  |5678
>
> I think this is called a "polymorphic join" but I could be wrong 
about
> that. I'd guess you could construct a rule or trigger to validate 
the

> foreign key data on insert/update but that's out of my skill area.

Hi Steve,

So in your solution the f_table column is just text which needs to be
validated by a custom trigger?


Hi,

Yup - that's exactly what I'm suggesting. Storing the text value of the 
related tables right in the table in question. It might seem insane, 
but in my experience it works out reasonably well. Ruby on Rails has 
popularized the approach, using it both in the data backend, as well as 
in the OO frontend (so Rugy object class to be instantiated is chosen 
by the text value of "f_table" for a given row - hence the 
polymorphism).


http://wiki.rubyonrails.org/rails/pages/UnderstandingPolymorphicAssociations

There are some situations where this approach could create problems but 
if in general all you're doing is select statements along these lines:


select * from images where
f_table = 'person' and f_id = '1234'

There's not much to go wrong. (Famous last words).

And regarding the custom validation by trigger, I'd think that would 
work just fine. I'm not an expert on triggers, rules and constraints in 
Pg though. (I do all my validation in the middleware, which might give 
some people here high blood pressure). :)


Keep us posted on which solution you choose and how it works out for 
you!


Steve 



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