Re: [SQL] Bizarreness at A2 Hosting

[John DeSoi]

On Mar 17, 2011, at 12:26 PM, Good, Thomas wrote:

 4) try to run pg_dump - it fails as it is unable to lock this other guy's 
 table
 
 tech suppt argues with me that their template is not hosed, that is the 
 nature of postgre, he said (not a typo, he omitted the trailing s)
 
 These guys are recommended by postgresql.org so I figured I'd try em...
 I have another account (for a client whom I support pro bono) and they do not 
 have this problem. New databases are empty as one would expect.
 
 Can someone tell me what exactly the problem is - I am waiting for a 
 supervisor to call me back and I'd like to lead him to the answer so he can 
 fix the problem.


I think you are right -- they likely have some one's stuff in the template 
database. On your account that does not have the problem -- is it on the same 
host?



John DeSoi, Ph.D.





-- 
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] pl/pgsql or control structures outside of a function?

[John DeSoi]

On Apr 3, 2009, at 5:03 PM, Peter Koczan wrote:


Is there any way to use PL/pgSQL code outside of a function?


No.




The reason I'm asking is that I'm porting some code from
sybase/isql/SQR, and it allows some control code structures to be used
in an sql script. For instance,


CASE might work for you.

http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html




John DeSoi, Ph.D.





--
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] simple text parsing function

[John DeSoi]

On Nov 21, 2008, at 3:35 PM, Kevin Duffy wrote:

select getrfs_bbcode('BPZ8 CURNCY  ',  NULL, NULL);
returns nothing.  I need to receive 'BPZ8 CURNCY  '  in  
this case.



What am I missing?



I think it is because of your choice of types:

select rtrim('BPZ8 CURNCY  ') like '%CURNCY'; -- true

select rtrim('BPZ8 CURNCY  ')::char(25) like '%CURNCY'; -- false

Interestingly, it works if you make your comparison using ~ '.*CURNCY'



John DeSoi, Ph.D.





--
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] doubt

[John DeSoi]

On May 17, 2007, at 5:43 AM, S balasankaravadivel wrote:
Shall i use \d command from the c program. If possible give me a  
example program.



If you want to use the \d command in a C program, link your program  
to libpq and grab the C source code for the \d command from psql.


Also, if you just need to know the SQL used to generate the command  
output you can use the following command:


\set ECHO_HIDDEN 1

Now all the SQL used in psql commands will be displayed. The SQL for  
the \d command is



=== psql 5 ===
\d

* QUERY **
SELECT n.nspname as Schema,
  c.relname as Name,
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'  
THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as  
Type,

  r.rolname as Owner
FROM pg_catalog.pg_class c
 JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**

  List of relations
Schema |   Name   |   Type   | Owner
+--+--+---
public | barcode  | table| user1
public | foo  | table| user1
public | foo_a_seq| sequence | user1
(3 rows)


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] PL/PGSQL Record type question

[John DeSoi]

You can use CREATE TYPE:

http://www.postgresql.org/docs/8.2/interactive/sql-createtype.html

Example from the documentation:

CREATE TYPE compfoo AS (f1 int, f2 text);


Then make your function return compfoo (or setof compfoo).  
Alternately, you can define your function with out or in/out  
parameters so you don't need the CREATE TYPE statement.





On May 11, 2007, at 10:42 AM, Gábriel Ákos wrote:


You might be looking for PostgreSQL RECORD data type.


Thanks. Give me an example please. I saw the documentation already.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] plpgsql array looping

[John DeSoi]

One problem (unless you intend to only look at every other element)  
is that you are incrementing idxptr explicitly in your loop. The FOR  
loop does that for you. This is the reason your output shows only  
even values.


John


On Apr 24, 2007, at 4:42 PM, Richard Albright wrote:


for idxptr in 1 .. array_upper(p_idxarray, 1)
loop
exit when p_idxarray[idxptr] = p_idx;
idxptr := idxptr +1;
raise notice 'idx ptr: %', idxptr;
end loop;




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [SQL] plpgsql function question

[John DeSoi]

If you use a plpgsql function to select the row you want to validate,  
it will make life much easier. Something like


...
$$
declare
  my_row a_row_type;
  is_ok integer;
begin
  select into my_row * from a_row_type where 
  is_ok := my_a_validate(my_row);
  return is_ok;
$$
...

On Apr 4, 2007, at 1:01 AM, A. Kretschmer wrote:


Because your function expects one parameter of your new type, you have
to CAST your data into this type:



test=# select * from my_a((1, 'foo', current_date)::a);
 my_a
--
1
(1 row)




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

   http://www.postgresql.org/about/donate

Re: [SQL] plpgsql function question

[John DeSoi]

It should work pretty much like you have it. You don't need a type;  
the table is already a type.


Something like:

create or replace function a_func (in p_row a) returns int as
$$
  if p_row.i ...
  if p_row.j ...
$$

If it does not work, show the error and I'll try to dig up an example.

John



On Apr 3, 2007, at 2:33 PM, Karthikeyan Sundaram wrote:


What I want is something like this
create or replace functinon a_func (in a%rowtype) returns int as
$$
    do the validation
$$
language 'plpgsql';

execute a_func(1, 'good','04/02/2007');




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] How to store a password encripted in a user defined table

[John DeSoi]

MD5 is built-in to PostgreSQL. It is what PostgreSQL itself uses to  
hash passwords. For example:


select md5('this is my password');

   md5
--
210d53992dff432ec1b1a9698af9da16
(1 row)



On Mar 1, 2007, at 6:06 AM, Eugenio Flores wrote:

Thanks Andrej. But how can I use such algoritms in postgresql? arey  
they defined in a function that I can call?


Or, do I have to code one of those algorithm to use it in my  
application?




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] Seeking quick way to clone a row, but give it a new pk.

[John DeSoi]

A pl/pgsql function can do this easily. Something like this (not  
tested):


create or replace function dup_my_table(old_key text, new_key text)  
returns text as

$$
declare
rec my_table;
begin;
select into rec * from my_table where key_field = old_key;
rec.key_field = new_key;
insert into my_table values (rec.*);
return new_key;
end;
$$ language plpgsql;



On Feb 7, 2007, at 4:21 PM, Bryce Nesbitt wrote:


I need to create some nearly identical copies of rows in a complicated
table.

Is there a handy syntax that would let me copy a existing row, but  
get a

new primary key for the copy?  I'd then go in an edit the 1 or 2
additional columns that differ.  The duplicate would be in the same
table as the original.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Some help with functions-syntax

[John DeSoi]

On Jan 18, 2007, at 2:38 AM, Jan Meyland Andersen wrote:

But the problem here is that the where-clause depends on the  
relkind. That

is why I'm trying to solve the problem this way.


I think that clause can be written as a subselect and added to the  
expression. Or just make that a separate function and AND the  
function call with your query expression.





How do I then write EXECUTE queries on multiple lines, if I go with  
this

solution?


You can make it a big multi-line string. Or you can build the string,  
something like:


declare
_sql

begin

_sql := _sql + 'select ...'
_sql := _sql + ' where ...'


But using the form without execute is much better. PostgreSQL will  
only have to plan the query the first time it is called. Using  
EXECUTE is much less efficient.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Some help with functions-syntax

[John DeSoi]

Take a look at the documentation and examples again:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control- 
structures.html#PLPGSQL-RECORDS-ITERATING


There are two forms to iterate over the query:

1. FOR target IN query LOOP
2. FOR target IN EXECUTE text_expression LOOP

In your code you have mixed the two together. You appear to be trying  
to concatenate a string on to the end of a query expression. My  
suggestion is to eliminate the string you created (_WHERECLAUSE) and  
add the proper conditions to the query expression you already have.





On Jan 17, 2007, at 5:33 PM, Jan Meyland Andersen wrote:


I have some problem with writing a function.

I have made this function which I can't get it to work.

This is probaly a triviel question but i'm new to plsql, so this is a
showstopper for me.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] ERROR: SELECT query has no destination for result data

[John DeSoi]

On Aug 31, 2006, at 9:00 AM, Ezequias Rodrigues da Rocha wrote:

select count(id) as numRegistros from base.emissor_ponto_venda  
where id = PontoVenda_Emissor;



declare numRegistros as an integer in the declarations section and  
rewrite the select:


select into numRegistros count(id) from base.emissor_ponto_venda  
where id = PontoVenda_Emissor;


See http://www.postgresql.org/docs/8.1/interactive/plpgsql- 
statements.html#PLPGSQL-SELECT-INTO





John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] ERROR: SELECT query has no destination for result data

[John DeSoi]

Sorry, try

select into numRegistros count(*) from base.emissor_ponto_venda where  
id = PontoVenda_Emissor;


The thing you are selecting into needs to match what you are  
selecting. So in your examples below, record is needed when you use  
* from  If you just need to look at one value:


select into num_em_pdv id from base.emissor_ponto_venda where id =  
PontoVenda_Emissor;



John


On Aug 31, 2006, at 10:07 AM, Ezequias Rodrigues da Rocha wrote:


Thank you John,

It only works using records. I don't know why.

When I put ('works well'):
select into num_em_pdv * from base.emissor_ponto_venda where id =  
PontoVenda_Emissor;
 if num_em_pdv.id is null then -- Se o emissor ponto venda  
passado não tem na base retorne 4

retorno:= 4;

When I put (don't works well):
num_em_pdv int4;
select into num_em_pdv * from base.emissor_ponto_venda where id =  
PontoVenda_Emissor;
 if num_em_pdv.id is null then -- Se o emissor ponto venda  
passado não tem na base retorne 4

retorno:= 4;

Reports the error:
ERROR:  missing FROM-clause entry for table num_em_pdv
CONTEXT:  SQL statement SELECT  num_em_pdv.id is null
PL/pgSQL function inserirpontos line 30 at if

Thank you John. I think I should stay with the record type.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] [PHP] PL/pgSQL and PHP 5

[John DeSoi]

Glad you found the problem.

On Aug 9, 2006, at 11:42 PM, PostgreSQL Admin wrote:

$connection-execute(SELECT insert_staff_b('$staff 
[insert_firstname]'::varchar));
$connection-execute(SELECT insert_staff_b('.$staff 
['insert_firstname'].'::varchar));


If you are creating SQL functions you want to call from PHP, you  
might be interested in this simple class:


http://pgedit.com/resource/php/pgfuncall


Then instead of all the quoting issue you have above, you could  
simply call your SQL function like a normal PHP method call:


$connection-insert_staff_b($staff['insert_firstname']);




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] [PHP] PL/pgSQL and PHP 5

[John DeSoi]

On Aug 9, 2006, at 10:36 PM, PostgreSQL Admin wrote:


select insert_staff_b('$_POST['firstname']::varchar)


Still I get this error:
Warning: pg_query(): Query failed: ERROR: function insert_staff_b 
(character varying) does not exist HINT: No function matches the  
given name and argument types. You may need to add explicit type  
casts.



Your select statement above has unbalanced single quotes. Assuming  
this is not really the issue, I would check the search_path and look  
at the function in psql or some admin tool to make sure the function  
name does not have different capitalization.





John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Triggers using PL/pgSQL

[John DeSoi]

Is it really necessary to build a SQL string and use execute? It  
seems you could just issue the INSERT statement.



On Jul 31, 2006, at 12:52 AM, Aaron Bono wrote:

CREATE OR REPLACE FUNCTION my_table_history_fn () RETURNS SETOF  
opaque AS

'
BEGIN
-- if a trigger insert or update operation occurs
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
execute
''INSERT INTO my_table_history ( '' ||
''my_table_id, '' ||
''my_value, '' ||
''create_dt '' ||
'') VALUES ( '' ||
'''' || NEW.my_table_id || '', '' ||
'''' || NEW.my_value || '', '' ||
''now() '' ||
'');''
;
RETURN NEW;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] Triggers using PL/pgSQL

[John DeSoi]

On Jul 31, 2006, at 10:59 AM, Aaron Bono wrote:


On 7/31/06, John DeSoi [EMAIL PROTECTED] wrote:
Is it really necessary to build a SQL string and use execute? It
seems you could just issue the INSERT statement.

I don't think so but there was some discussion a week or two ago  
about mixing variables and using execute.  I am curious, does  
anyone know what the best approach is?


I did not test with older versions, but it seems to work fine with 8.1:


CREATE OR REPLACE FUNCTION my_table_history_fn () returns trigger as
'
BEGIN
-- if a trigger insert or update operation occurs
IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
insert into my_table_history (
my_table_id,
my_value,
create_dt
) VALUES (
NEW.my_table_id,
NEW.my_value,
now()
);
RETURN NEW;
END IF;
END;
'
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


insert into my_table values (1, 'test1');
insert into my_table values (2, 'test2');
update my_table set my_value = 'test3' where my_table_id = 1;
select * from my_table_history;

=== psql 9 ===
my_table_id | my_value | create_dt
-+--+
   1 | test1| 2006-07-31 11:47:33.080556
   2 | test2| 2006-07-31 11:47:48.221009
   1 | test3| 2006-07-31 11:48:21.029696
(3 rows)




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] sessions and prepared statements

[John DeSoi]

On Jun 15, 2006, at 11:49 AM, chester c young wrote:

in PHP for example, where there are multiple sessions and which you  
get is random:


how do you know if the session you're in has prepared a particular  
statement?


and/or how do you get a list of prepared statements?

last, is there any after login trigger that one could use to  
prepare statements the session would need? or is this a dumb idea?


If you are using pooled connections, I don't think there is a  
reasonable way you could managed prepared statements across requests.  
You'll probably want to just prepare the ones you need for the  
current request and discard them when the request ends.


I have a short article where you might find some useful information  
for managing prepared statements:


http://pgedit.com/resource/php/pgfuncall

You might also post your question to PostgreSQL PHP list -- probably  
more PHP expertise there.


Best,

John




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] global variables in plpgsql?

[John DeSoi]

On Apr 10, 2006, at 9:17 PM, [EMAIL PROTECTED] wrote:


So, a couple of questions
1) Can you declare global values from plpgsql?
2) If so, is there a way of avoiding namespace pollution?
   (perhaps the equivalent to Oracle's use of plsql package
   variables)


plpgsql does not have global variables.


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Problem using set-returning functions

[John DeSoi]

On Mar 27, 2006, at 5:41 AM, Markus Schaber wrote:


navteq=# select foo,generate_x(bar) from test;
ERROR:  set-valued function called in context that cannot accept a set
CONTEXT:  PL/pgSQL function generate_x line 5 at return next

However, it is fine to call other set returning functions in the same
context:



With SRFs, you need to specify what you want to select. In other  
words if you are calling generate_x(bar) you need select * from  
generate_x(bar) -- select generate_x(bar) will not work.


So for your query I think you need something like:

select foo, (select x from generate_x(bar)) from test;



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Problem using set-returning functions

[John DeSoi]

Hi Markus,

On Mar 27, 2006, at 9:00 AM, Markus Schaber wrote:


So, then, why does it work with generate_series() and dump()?


I'm not sure. All I know is I spent a while the other day puzzling  
over the same error message you had and finally realized I had to add  
a select expression to fix it.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Copying a row within table

[John DeSoi]

On Mar 14, 2006, at 2:19 AM, Aarni Ruuhimäki wrote:

testing=# INSERT INTO foo (foo_1, foo_2, foo_3 ...) (SELECT foo_1,  
foo_2,

foo_3 ... FROM message_table WHERE foo_id = 10);
INSERT 717286 1
testing=#

Is there a fast way to copy all but not the PK column to a new row  
within the

same table so that the new foo_id gets its value from the sequence ?



Here is an example using a plpgsql function:

create or replace function test_duplicate (p_id integer)
returns integer as $$
declare
tt test%rowtype;
begin
select into tt * from test where id = p_id;
tt.id := nextval(pg_get_serial_sequence('test', 'id'));
insert into test values (tt.*);
return tt.id;
end;
$$ language plpgsql;






John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] Executing plpgsql scripts using psql, is that possible?

[John DeSoi]

On Jan 16, 2006, at 5:35 AM, Daniel CAUNE wrote:

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:



If you want to execute a plpgsql function from a file using psql,  
just call it with SELECT. So your file might have:


create or replace function my_function(params integer)
returns integer as $$
DECLARE
  V_MyObjectID bigint;
BEGIN
  V_MyObjectID := RegisterMyObject('a string', 'another string');
  AddObjectProperty(V_MyObjectID, 'a string');
  AddObjectProperty(V_MyObjectID, 'another string');

END;
$$ language plpgsql;


SELECT my_function(1);


and then psql -f script.sql my_db



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] Executing plpgsql scripts using psql, is that possible?

[John DeSoi]

Daniel,

On Jan 16, 2006, at 8:55 PM, Daniel CAUNE wrote:

Yes, but that requires creating a function while I would prefer not  
having do so, as I said in my previous mail: I mean, without  
creating a function that wraps the whole, of course! :-).  Why?   
Actually this is not a function; this is a script that inserts  
static data into dimension tables such as Country, Language, etc.


Sorry I misunderstood the question.



So, I completely understand that I can write an SQL script that:

  1 - creates a function that wraps SQL code that inserts static  
data into dimension tables.

  2 - executes that function
  3 - destroys that function

But actually that is a bit weird, isn't it?


\copy is the easiest way in psql to populate tables.

If you need more control, maybe copy the data to temp tables and then  
write plpgsql as needed to insert the data into the final tables.





John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] info is a reserved word?

[John DeSoi]

I have two identical functions below, the only difference is I  
declared my variable name to be 'info' instead of 'stuff'. I could  
not find anywhere in the docs that 'info' has any special meaning.  
Did I miss it?


create type my_info as (
a text,
b text
);


-- this works
create or replace function my_stuff ()
returns my_info as $$
declare
stuff my_info;
begin
stuff.a := 'hi';
stuff.b := 'there';
return stuff;
end;
$$ language plpgsql;


create or replace function my_stuff ()
returns my_info as $$
declare
info my_info;
begin
info.a := 'hi';
info.b := 'there';
return info;
end;
$$ language plpgsql;

Evaluating this definition gives:

psql:16: ERROR:  syntax error at or near info at character 71
psql:16: LINE 4:  info my_info;
psql:16:  ^

pg 8.1.1, OS X 10.4.3



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] catching errors in function

[John DeSoi]

On Oct 6, 2005, at 2:55 AM, padmanabha konkodi wrote:


i have written function in which while executing it may throw error.
if the error thrown i want rollback the transaction if not i want  
commit.


how can i achive this task.

how can catch exception thrown in the function



The function itself cannot start or rollback a transaction (functions  
are always executed in the context of a transaction). But I think you  
can use a SAVEPOINT to accomplish your request:


http://www.postgresql.org/docs/8.0/interactive/sql-savepoint.html

And see this page on how to catch exceptions:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-control- 
structures.html#PLPGSQL-ERROR-TRAPPING



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Please help, can't figure out what's wrong with this function...

[John DeSoi]

On Sep 12, 2005, at 8:14 AM, Moritz Bayer wrote:


I get the following error:
ERROR:  missing .. at end of SQL expression


it looks like your for loop is being interpreted as the integer  
variant, e.g. for i in 1..10 loop




CREATE TYPE public.ty_stadtlandflussentry AS (



DECLARE objReturn ty_stadtlandflussentry;



Maybe it needs to be:

declare objReturn public.ty_stadtlandflussentry%rowtype;



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Number of rows in a cursor ?

[John DeSoi]

On Aug 24, 2005, at 6:31 AM, Bo Lorentsen wrote:

How sad, then I have to repeat the query, first for counting and  
last for data fetch :-(


No, you can use the MOVE command and read how many rows you moved  
with something like


MOVE LAST IN mycursor;

http://www.postgresql.org/docs/8.0/interactive/sql-move.html



John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] incorrect syntax for 'plpgsql' function to test boolean values

[John DeSoi]

On Aug 6, 2005, at 10:52 PM, Ferindo Middleton Jr wrote:


ERROR:  operator does not exist: boolean == boolean
HINT:  No operator matches the given name and argument type(s). You  
may need to add explicit type casts.

CONTEXT:  SQL statement SELECT  (( $1  == true)  ( $2  == true))
PL/pgSQL function  
trigger_insert_update_registration_and_attendance line 13 at if


What is wrong with my syntax above?



Too much C programming :). You just want a single equal sign.

select true = true;
?column?
--
t
(1 row)




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] echo/printf function in plpgsql

[John DeSoi]

On Jul 19, 2005, at 11:58 AM, Andreas Joseph Krogh wrote:

I see. Can I make the ouput somehow less verbose? It spits out a  
lot of noise

for each NOTICE:


If you just want to output some information to the log, you can use  
something like this:


raise log 't is %', t;

If I recall correctly, the values to be inserted into the format  
string can only be variables, not expressions.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [SQL] Error on dynamic code.

[John DeSoi]

On Jul 14, 2005, at 3:46 AM, Mark J Camilleri wrote:





The funny thing is that the documentation I read about SELECT INTO  
and RECORD types give the following example, amongst others:

See the section below that on EXECUTE:

The results from SELECT commands are discarded by EXECUTE, and  
SELECT INTO is not currently supported within EXECUTE. So there is  
no way to extract a result from a dynamically-created SELECT using  
the plain EXECUTE command. There are two other ways to do it,  
however: one is to use the FOR-IN-EXECUTE loop form described in  
Section 35.7.4, and the other is to use a cursor with OPEN-FOR- 
EXECUTE, as described in Section 35.8.2.




John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] How do I quit in the middle of a SQL script?

[John DeSoi]

On May 20, 2005, at 1:22 PM, Wei Weng wrote:
Say if I want to add a small snip of code in front of the sql script 
generated by the pg_dump, to check for something then if the condition 
doesn't match, the script terminates right away. (Without actually 
doing the restoring stuff that the following large chunk is supposed 
to do)

Can I do that?
Put this at the start of the file to make psql stop if there is an 
error:

\set ON_ERROR_STOP 1
And is it a good idea to add arbitrary code to the database dump sql 
script?
No problem if you know what you are doing and/or have good backups :)

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] php wrapper

[John DeSoi]

On Apr 21, 2005, at 8:00 AM, Mauro Bertoli wrote:
Hi, I need a PHP wrapper for PostgreSQL...
I found 1000 small +/- identicals wrappers but
incompleted
There's an ufficial or an suggested PHP wrapper?

Why not use the built-in PHP functions for PostgreSQL? If by wrapper 
you mean an abstraction to support other databases, what other systems 
do you need to support?

PEAR seems to have a nice databases abstraction layer (DB.php). Drupal 
uses this to support PostgreSQL and MySQL.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] getting count for a specific querry

[John DeSoi]

On Apr 8, 2005, at 3:37 PM, Joel Fradkin wrote:
I don't think my clients would like me to aprox as it is a count of 
their
records. What I plan on doing assuming I can get all my other problems 
fixed
(as mentioned I am going to try and get paid help to see if I goofed 
it up
some where) is make the count a button, so they don't wait everytime, 
but
can choose to wait if need be, maybe I can store the last count with a 
count
on day for the generic search it defaults to, and just have them do a 
count
on demand if they have a specific query. Our screens have several 
criteria
fields in each application.
Here is an interface idea I'm working on for displaying query results 
in PostgreSQL. Maybe it will work for you if your connection method 
does not prevent you from using cursors. I create a cursor an then 
fetch the first 1000 rows. The status display has 4 paging buttons, 
something like this:

|  rows 1 - 1000 of ?  |
The user can hit the next button to get the next 1000. If less than 
1000 are fetched the ? is replaced with the actual count. They can 
press the last button to move to the end of the cursor and get the 
actual count if they need it. So here the initial query should be fast, 
the user can get the count if they need it, and you don't have to 
re-query using limit and offset.

The problem I'm looking into now (which I just posted on the general 
list) is I don't see a way to get the table and column information from 
a cursor. If I fetch from a cursor, the table OID and column number 
values are 0 in the row description. If I execute the same query 
directly without a cursor, the row description has the correct values 
for table OID and column number.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] getting count for a specific querry

[John DeSoi]

On Apr 9, 2005, at 11:43 AM, Bob Henkel wrote:
Forms also offers a button that say get hit count. So if you really 
need to know the record count you can get it without moving off the 
current record.
That's a good idea too. Maybe in my interface you could click on the ? 
to get the count without changing the rows you are viewing.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] Question on triggers and plpgsql

[John DeSoi]

On Apr 7, 2005, at 5:45 PM, Carlos Moreno wrote:
The thing seems to work -- I had to go in a shell as user
postgres and execute the command:
$ createlang -d dbname plpgsql
(I'm not sure I understand why that is necessary, or
what implications -- positive or negative -- it may have)
As a security measure, no pl language is available by default. What you 
did is correct. There is not much (any?) risk with pl/pgsql, so you can 
install it in template1 so it will be available in any new database you 
create.

Am I doing the right thing?  Have I introduced some sort
of catastrophe waiting to happen?
I did not notice any problems.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Question on triggers and plpgsql

[John DeSoi]

On Apr 8, 2005, at 9:41 AM, Sean Davis wrote:
Just one detail, but in the form of a question. In the original 
posting, I think the trigger was doing the logging for something 
happening on a table as a before insert or update--I may be wrong on 
that detail.  I would think of doing such actions AFTER the 
update/insert.  In the world of transaction-safe operations, is there 
ANY danger in doing the logging as a BEFORE trigger rather than an 
AFTER trigger?

Good point. I think both will work in this case and it would depend on 
the application if it makes a difference. You definitely want an AFTER 
trigger if you need to see the final state of the row before making 
changes. In this case the assignment of the column does not depend on 
any other factors so it would not seem to matter. But I agree from a 
semantics point of view, an AFTER trigger might be a little better for 
this.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Question on triggers and plpgsql

[John DeSoi]

Tom,
Thanks for setting the record straight. It has been a while since I 
have written a trigger and I forgot that you can't modify the row in 
the AFTER trigger. Makes perfect sense.

For the record, here is what the docs say:
Typically, row before triggers are used for checking or modifying the 
data that will be inserted or updated. For example, a before trigger 
might be used to insert the current time into a timestamp column, or to 
check that two elements of the row are consistent. Row after triggers 
are most sensibly used to propagate the updates to other tables, or 
make consistency checks against other tables. The reason for this 
division of labor is that an after trigger can be certain it is seeing 
the final value of the row, while a before trigger cannot; there might 
be other before triggers firing after it. If you have no specific 
reason to make a trigger before or after, the before case is more 
efficient, since the information about the operation doesn't have to be 
saved until end of statement.

It might be worth adding a sentence here that explicitly states 
modifications can only be made in the BEFORE trigger. I did not see 
that anywhere else in the document.

On Apr 8, 2005, at 10:36 AM, Tom Lane wrote:
No, actually Carlos wanted to do
new.last_modified = now();
so he *must* use a BEFORE trigger --- AFTER is too late to change the
data that will be stored.
Generalizing freely, I've seen three basic uses for triggers:
	1. Modify the data that will be stored.
	2. Check that data is valid (eg, consistent with another table).
	3. Propagate updates in one place to other places.
Clearly #1 must be done in BEFORE triggers.  #2 and #3 could be done
either way.  They are often done in AFTER triggers because that way you
*know* that any case-1 triggers have done their work and you are 
looking
at the correct final state of the row.  But you could do them in a
BEFORE trigger if you were willing to assume that no later-fired 
trigger
would make a change that invalidates your check or propagation.  AFTER
triggers are relatively expensive (since the triggering event state has
to be saved and then recalled) so I could see making that tradeoff if
performance is critical.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] pl/pgsql problem with return types

[John DeSoi]

On Mar 11, 2005, at 5:54 AM, Juris Zeltins wrote:
   FOR P IN select pageid from pages
This way you are only getting the pageid column. I think what you want 
is

FOR P in select * from pages
so that P contains the complete pages record.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] [ADMIN] Postgres schema comparison.

[John DeSoi]

On Mar 7, 2005, at 4:33 AM, Stef wrote:
I have the wonderful job of re-synch'ing  all the schemas out there not
conforming to the master. I've looked everywhere for something that
will help doing this. I'm specifically looking for a way to do a 
sumcheck
or something similar on tables and/or schema as a whole to be able to
do a table comparison with the master database.

Develop a function that builds a string describing the tables/schemas 
you want to compare. Then have your function return the md5 sum of the 
string as the result. This will give you a 32 character value you can 
use to determine if there is a mismatch.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send unregister YourEmailAddressHere to [EMAIL PROTECTED])

Re: [SQL] [ADMIN] Postgres schema comparison.

[John DeSoi]

On Mar 7, 2005, at 10:09 AM, Stef wrote:
Is it possible to somehow pass the output of : \d [TABLE NAME]
to this function? If not, what would return me consistent text
that will describe the columns, indexes and primary keys of a table?
I'm not sure you can use \d directly, but if you startup psql with the 
-E option it will show you all the SQL it is using to run the \d 
command. It should be fairly easy to get the strings you need from the 
results of running a similar query. The psql source is a good place to 
look also.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] RE: [SQL] trrouble inserting stuff like é

[John DeSoi]

On Feb 18, 2005, at 11:15 AM, Joel Fradkin wrote:
How do I tell the connection to use Unicode?
Try
SET client_encoding TO 'UNICODE';
http://www.postgresql.org/docs/8.0/interactive/sql-set.html
But it should default to the database encoding, so I'm not sure if that 
is the problem.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] How to iterate through arrays?

[John DeSoi]

On Feb 9, 2005, at 9:26 AM, NosyMan wrote:
I think the problem is not there. I got the following error:
'ERROR:  syntax error at or near [ at character 1234', the line is: 
RAISE
NOTICE '% ...',update_query_params[1];
The problem here is RAISE, NOTICE, etc. can only use simple variables 
in the format string. Try it like:

myTextVar := update_query_params[1];
NOTICE '% ...',myTextVar;
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] Updating selected record

[John DeSoi]

On Feb 8, 2005, at 9:05 AM, Levente Lajko wrote:

I have problem with a table from where I select specific field data
 SELECT filed1, field2 FROM tbl WHERE progress = 1 LIMIT 1
through a perl script or multiple instances of that script. The  
script sets the progress value of the progressed record to 0, so that  
other processes not to access that row. I presume I would need some  
locking solution, only I havent found the real one.

 
I hope somebody has a bright idea.
Maybe you are looking for SELECT FOR UPDATE as in
SELECT filed1, field2 FROM tbl WHERE progress = 1 LIMIT 1 FOR UPDATE;
This locks the row for the remainder of the transaction. See
http://www.postgresql.org/docs/8.0/interactive/sql-select.html#SQL-FOR- 
UPDATE


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [SQL] error in function!!

[John DeSoi]

On Jan 31, 2005, at 1:59 PM, Ing. Jhon Carrillo wrote:
ERROR:  function tschema.sp_actualizar_contacto(integer, unknown, 
unknown, unknown, unknown, unknown, unknown, integer, 
unknown, unknown, unknown, unknown, unknown, unknown, 
unknown, unknown, unknown, unknown, unknown, integer, 
integer) does not exist
HINT:  No function matches the given name and argument types. You may 
need to add explicit type casts.
 
 
I need to know if the insert sentence was sucesfull, how do i do?
 
This message is telling you that your function call using 
tschema.sp_actualizar_contacto was not correct. You must have all 
parameters in the function call and they must all be the correct type. 
So the message above says the first parameter passed was an integer, 
but your function expects the first parameter to be text.

Note that you can remove all of the ALIAS declarations and use 
parameter name directly.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[SQL] plpgsql select into with multiple target variables

[John DeSoi]

The docs say:
The result of a SELECT command yielding multiple columns (but  only one 
row) can be assigned to a record variable, row-type  variable, or list 
of scalar variables. This is done by:
SELECT INTO target  select_expressions  FROM ...;
where target can be a record variable, a row  variable, or a 
comma-separated list of simple variables and  record/row fields. The 
select_expressions  and the remainder of the command are the same as in 
regular SQL.

So, I'm trying to do the list of scalar variables target, but I can't 
get the syntax right. Something like

SELECT into varx, vary, varz,
  colx, coly, colz, FROM 
I've tried parens and various other things but no luck.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Moving from Transact SQL to PL/pgsql

[John DeSoi]

On Jan 23, 2005, at 10:22 PM, Duffy House wrote:
The first issue will be getting up to speed on PL/pgsql.  Where can I 
find
primer on PL/pgsql, with lots of examples? How silimar is PL/pgsql to 
PL/SQL
under Oracle?
The PostgreSQL documentation is the place to start:
http://www.postgresql.org/docs/8.0/interactive/plpgsql.html
The PostgreSQL distribution has a file with some examples in it:
src/test/regress/sql/plpgsql.sql
The pgEdit distribution (http://pgedit.com/download) has a fairly 
extensive plpgsql example for importing and analyzing web server logs.

I'm not familiar with Oracle, but there is a porting section in the 
documentation:

http://www.postgresql.org/docs/8.0/interactive/plpgsql-porting.html

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [SQL] editors with colum positioning for debugging?

[John DeSoi]

On Jan 22, 2005, at 5:12 PM, Joel Fradkin wrote:
My question is I dont see an easy way to find an error.
Typical error text is syntax error at or near trans at character 825
But determining character 825 is a little slow by hand.
In pgEdit you can use the Goto Point command to jump to a specific 
offset in the file. I hope to have something better in the near future 
to automatically move the cursor for you when an error is detected.

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] Problems with Quotes

[John DeSoi]

On Jan 12, 2005, at 1:08 PM, Kieran Ashley wrote:
I've tried using the replace() function to get rid of the , but I 
can't figure out how to use it without throwing an error.  I tried

replace(col_name, '\', '')
and several other permutations but to no avail, do I need to use 
something like an ASCII character code in order to get rid of a quote? 
 If so which one, and if not, is there a better solution?


Try '' as in
select replace('this is it', '', '');
  replace

 this is it
(1 row)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Problems with Quotes

[John DeSoi]

On Jan 12, 2005, at 2:00 PM, Kieran Ashley wrote:
I'm still not entirely sure _why_ that works, but it does... so I can 
go home now!  ;)

You should look at section 37.2.1 in the current docs. 8.0 has a new 
dollar quoting feature which makes this easier to deal with.

http://www.postgresql.org/docs/7.4/static/plpgsql-development-tips.html
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [SQL] Table History

[John DeSoi]

On Dec 17, 2004, at 1:23 AM, Richard Sydney-Smith wrote:
I expect this has been done MANY times and I wonder if a general 
purpose trigger exists or if not then can someone point me to an 
example set of triggers?

I'm not aware of a general purpose trigger for this. If you just want 
some extra trigger examples other than what is in the documentation, 
there is a test file in the distribution with quite a few:

src/test/regress/sql/plpgsql.sql
Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

Re: [SQL] Hide schemas and tables

[John DeSoi]

On Dec 13, 2004, at 5:25 AM, Markus Schaber wrote:
Is there any way to hide schemas and relations a user does not have
access privileges for?
I suspect that the client (in this case, unavoidably excel via OLAP and
ODBC) gets this information via querying meta tables, so there is no 
way
to protect foreign schemas and relations from beeing seen.
Yes, you are correct. I'm not aware of any way to hide this information.
Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] Postgres Doubt

[John DeSoi]

On Sep 27, 2004, at 11:27 PM, sreejith s wrote:
Hello,
  I am new to PostgreSQL. I have a doubt. Now i am doing one
project in .NET technology with postgres(linux) as database. I am
using pgADMIN and pgManager as tools for database releted activities.
Now i am able to create functions with 'sql' language. When i select
'pgsql' as language for creating query, an error poping up. How to
create Query using 'pgsql' language. if any new tools need to be
installed. Plz rectify. Mail me back at [EMAIL PROTECTED]

You need to use the createlang utility to add pgsql to your database.
see
http://www.postgresql.org/docs/current/interactive/app-createlang.html
http://www.postgresql.org/docs/current/interactive/sql- 
createlanguage.html

You generally want to do this for template1 so it will be available in  
any new database you create.

Best,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] colored PL with emacs

[John DeSoi]

On Aug 31, 2004, at 1:23 PM, Manuel Sugawara wrote:

I have SQL highlighting, but what I want are colors for the PL/pgSQL
key words. It would make PL programming much easier.
Since the Pl/PgSQL code is quoted (x)emacs paints the whole thing
using the string face. Delete one of the apostrophes delimiting the
code while editing and you should get some highlighting.
I'm working on an editor specifically designed for PostgreSQL and 
plpgsql programming. It colors syntax within function strings and helps 
get the quoting right (8.0 dollar quoting is also supported). The 
editor is based on emacs but it also has emulation modes that are more 
friendly for those who are not familiar with emacs. I hope to start 
beta testing the Mac OS X version next week, followed by a Windows 
version probably in October. I have not yet decided if I will do a 
Linux version, but it should be possible. The estimated price for this 
product is $65.

Send me an email if you are interested in beta testing the Mac OS X 
version.

Best,
John DeSoi, Ph.D.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] sleep function

[John DeSoi]

Hi Greg,
On Aug 22, 2004, at 11:52 PM, Greg Stark wrote:
test=# create or replace function sleep(integer) returns integer as 
'return sleep(shift)' language plperlu;
CREATE FUNCTION

The original request was for something in built-in to PostgreSQL. I 
have not used plperl (or any of the other pl languages), but I assume 
there will be additional installation and configuration issues to use 
them. But thanks for the example, it will be helpful if I need 
something more processor friendly.

Best,
John DeSoi, Ph.D.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] sleep function

[John DeSoi]

On Aug 10, 2004, at 10:57 AM, Bruce Momjian wrote:
I can't think of one, no.  I think you will have to use one of the
server-side languages and call a sleep in there.

This is no good in the real world since it pounds the CPU, but it 
worked well enough for my testing purposes.

Best,
John DeSoi, Ph.D.

create or replace function sleep (integer) returns time as '
declare
seconds alias for $1;
later time;
thetime time;
begin
thetime := timeofday()::timestamp;
later := thetime + (seconds::text || '' seconds'')::interval;
loop
if thetime = later then
exit;
else
thetime := timeofday()::timestamp;
end if;
end loop;
return later;
end;
' language plpgsql;
---(end of broadcast)---
TIP 8: explain analyze is your friend

[SQL] sleep function

[John DeSoi]

Is there a sleep function of some kind? I wanted to simulate a query 
taking a long time to execute for testing purposes.

Thanks,
John DeSoi, Ph.D.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [SQL] Object-relational features

[John DeSoi]

On Mar 13, 2004, at 12:30 PM, Yasir Malik wrote:

For
example, using create type as is totally worthless because you can't 
use
it as a field type in a table; you can't compose in another create 
type
as; and you can't inherit another composite type.  The only way to 
create
a true type is to use create type and write C code as a shared 
object,
so I'm basically doing everything C, which is not something I want to 
do.


I'm not sure if this is what you are looking for, but it shows how to 
create a column type based on the text type. So your selects will 
return the column type as your custom type and you can process the 
content accordingly. From reading the docs (and asking on the list) I 
did not think this was possible either without writing external code in 
C. But a post about something else finally provided the clues I needed 
to get it working.

Best,

John DeSoi, Ph.D.


test=# create or replace function lispin(cstring, oid, int4) returns 
lisp as 'varcharin' language 'internal' immutable strict;
NOTICE:  type lisp is not yet defined
DETAIL:  Creating a shell type definition.
CREATE FUNCTION
test=# create or replace function lispout(lisp) returns cstring as 
'varcharout' language 'internal' immutable strict;
NOTICE:  argument type lisp is only a shell
CREATE FUNCTION
test=# create type lisp (input=lispin, output=lispout, 
internallength=variable);
CREATE TYPE
test=# create table tst (a lisp);
CREATE TABLE
test=# insert into tst (a) values ('1');
INSERT 18499 1
test=# insert into tst (a) values ('(+ 5 5)');
INSERT 18500 1
test=# select * from tst;
a
-
 1
 (+ 5 5)
(2 rows)

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [SQL] A simple way to Create type ...?

[John DeSoi]

On Tuesday, September 16, 2003, at 05:27 PM, Christopher Browne wrote:

What you want instead is CREATE DOMAIN.

flexreg=# create domain addresstype varchar(50);


The problem here is that you can't tell the difference between a 
addresstype column and a varchar(50) column in the row description 
information returned by SELECT. All columns just look like varchar(50). 
It would be nice if there was something as easy as CREATE DOMAIN but 
worked more like CREATE TYPE.

Best,

John DeSoi, Ph.D.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match