tly 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 i
ction test ()
returns void as $$
declare
rec record;
begin
for rec in select * from whatever loop
insert into some_table values (rec.*);
end loop;
end;
$$ language plpgsql;
John DeSoi, Ph.D.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.or
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
t 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 mailin
7;, 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**
List of relations
Schema | Name | Type | Owner
+--+--+---
public | barcode
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 Tool
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 f
, 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
ple.
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');
Jo
e:
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
, 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 broa
x27;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
-
, 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
ot;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
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
ike 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
have different capitalization.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
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
a
''my_value, '' ||
''create_dt '' ||
'') VALUES ( '' ||
'''''''' || NEW.my_table_id || '''''', '' ||
''''
ably
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
lobal variables.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
o 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
e:
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
:= 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
he final tables.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
;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
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: in
://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
uot; 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)---
T
/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
e))"
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
27;, 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 plan
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
t 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 :)
f 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
---
thout 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
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.
J
ively 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
.
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
ng 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
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
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
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])
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 probl
y 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]
;
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
ct 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
ke
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 arc
racle, 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
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)-
nsive example for importing and
analyzing web server logs using only SQL and PL/pgSQL. Look in the
pgEdit/Examples/web folder after installation.
http://pgedit.com/download
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broa
l.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
ail, 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', '"', '');
repl
ant
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)
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 checke
sql 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.
. 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
e 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)---
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
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
t 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 clue
ption
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
62 matches
Mail list logo
