o something like this:
CREATE FUNCTION add_user(varchar(20),varchar(20),varchar(20),int4,int4)
RETURNS VARCHAR AS
'INSERT INTO usr
(user_name,first_name,last_name,permission_set_id,customer_id)
VALUES
($1,$2,$3,$4,$5);
SELECT ''created user ''|| $1::VARCHA
ent, which could return anything. All the example
does is return a string telling you what you've just done.
Ian Barwick
> > On Wednesday 10 July 2002 21:59, David Durst wrote:
> >> Is there anyway to create a insert function?
> >> I am trying:
> >> CREATE FUN
>
> is there an easier way t get a list of tables ?
Start psql with the -E option and issue \dt
This gives you the statement(s) used internally by psql.
Ian Barwick
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--+
1 row in set (0.01 sec)
mysql> select * from foo where ch = 'AA';
+--+--+
| ch | vc |
+--+--+
| aa | AA |
+--+--+
1 row in set (0.00 sec)
mysql> select * from foo where vc = 'aa';
+--+--+
| ch | vc |
+--+--+
| aa | A
recall any other
database apart from MySQL which default to case-insensitive
CHAR or VARCHAR columns.
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail c
On Friday 27 September 2002 05:19, Tom Lane wrote:
> Ian Barwick <[EMAIL PROTECTED]> writes:
> > Anyone know what the ANSI standard is? I don`t recall any other
> > database apart from MySQL which default to case-insensitive
> > CHAR or VARCHAR columns.
>
> I b
nt.
This may give you a better idea of what is happening.
I would also strongly recommend replacing the double quotes
with pairs of single quotes, e.g. ''OK'' instead of "OK" .
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
data type. Annoying, really.
If I replace the return_array allocations in the above example with this
line:
return_array := ''{ ''''test'''', ''''test 1'''', ''''test 2''&#
MAX(pos)+1 ) IS NULL THEN 0
ELSE (SELECT MAX(pos)+1 )
END)
)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
tgresql.org/docs/view.php?version=7.3&idoc=0&file=release.html
"Allow functions to return multiple rows (table functions) (Joe)"
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please s
quot;perl" format.
You can get format in the style above by executing
\pset border 2
in psql.
(SQL*Plus fans should issue
\pset border 0
and recompile psql without readline support to simulate
an Oracle environment ;-)
Place this setting in your .psqlrc file to make it permanent.
Ian Barwick
[E
ascii('Â', 'LATIN1')
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ou define logins.login as VARCHAR instead of CHAR?
See also:
http://www.postgresql.org/docs/faqs/FAQ.html#4.14
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
stration probably required).
Of course you could also use a set returning function a la:
CREATE OR REPLACE FUNCTION months() RETURNS SETOF INT AS '
BEGIN
FOR i IN 1..12 LOOP
RETURN NEXT i;
END LOOP;
RETURN;
END;' LANGUAGE 'plpgsql';
Ian Barwick
[EMAIL PROTEC
.keyword = a2.keyword
> )
How about (untested):
SELECT a1.id
FROM aap a1
WHERE id = (SELECT MAX(id) FROM aap a2
WHERE a2.keyword = a1.keyword)
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 6: Have you searched
SET col2 = (SELECT val2 FROM table WHERE id = 34)
^^^
> WHERE id = 35;
The second "SET" is not necessary or allowed and is causing the update to
fail.
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)
IF (TG_OP=DELETE AND check_count > 1) THEN
>
(...)
> What am I making wrong ?
Not quoting? Try:
IF (TG_OP=''DELETE'' AND check_count > 1) THEN
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2:
eign Keys *g*
>
> One more point in favor for PostgreSQL :)
I've been here before, this thread:
http://archives.postgresql.org/pgsql-sql/2003-04/msg00159.php
might be of interest.
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
ults could be table1...
> table2.pk2
> table1... NULL
>
> Doable?
You need an OUTER JOIN, see e.g.
http://www.postgresql.org/docs/7.3/static/tutorial-join.html
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 9: the plan
: ERROR: JOIN/ON clause refers to "u", which is not part of
> JOIN SELECT u.uid, u.txt, p.val FROM u,a LEFT JOIN p ON p.uid = u.uid AND
> p.pkey = a.pkey WHERE a.id = u.aid;
Try:
SELECT u.uid, u.txt, p.val
FROM u
INNER JOIN a ON (a.id=u.aid)
LEFT JOIN p ON (p.pkey=a.pkey AND p.
tem on the TODO list IIRC.
Using ALIAS FOR might make things easier though, something
along the lines of:
DECLARE
address_id ALIAS FOR $1;
address ALIAS FOR $2;
...
BEGIN
...
END;
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)--
om ftrans
>
> this returns two columns: src = 'T' and pres which has either the value 'U'
> or 'P'
>
> how would I express this in postgresql?
"CASE":
http://www.postgresql.org/docs/7.3/static/functions-conditional.html
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
atei' DELIMITER '|';
Da musst Du dann in den Spalten mit einem NULL-Wert \N einfügen, also:
2|Test|18.5|\N|usr02
Das kann auch ein anderer Wert sein, z.B.
COPY x FROM '/pfad/zur/datei' DELIMITER '|' NULL 'NULL';
um NULL schreiben zu koennen
ar nichts, ist aber sonst recht zuverlaessig).
Ian Barwick
[EMAIL PROTECTED]
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
min:
http://phppgadmin.sourceforge.net/
Ian Barwick
[EMAIL PROTECTED]
Wenn Sie Ihr Abonnement fuer diese Gruppe kuendigen moechten, senden
Sie eine E-Mail an:
[EMAIL PROTECTED]
Die Nutzung von Yahoo! Groups ist Bestandteil von
http://de.docs.yahoo.com/info/utos.html
-
least with testing and documentation.
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
ernal program only to list the databases. I've googled about this
> problem but I only found the '-l'-way to this this.
>
> Ideas? Solutions?
Start psql with the -E switch, and it shows the SQL used to generate
the output from psql's slash commands.
can be found e.g. here:
http://www.postgresql.org/docs/current/static/monitoring.html
Ian Barwick
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
FROM transactions WHERE shippingdate> '2004-06-08' AND
transtype='Sale';
Ian Barwick
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
field varchar(2) check (field in
(null, 'a','b','c')));
CREATE TABLE
test=> insert into consttest values ('xx');
INSERT 408080 1
test=> SELECT * from consttest ;
field
---
xx
(1 row)
Not sure what logic is driving this).
Ian Barwick
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
On Sat, 11 Dec 2004 07:47:51 -0800 (PST), Stephan Szabo
<[EMAIL PROTECTED]> wrote:
> On Sat, 11 Dec 2004, Ian Barwick wrote:
>
> > (Oddly enough, putting the NULL in the CHECK constraint seems
> > to make the constraint worthless:
> > test=> create table consttes
dodgy hardware) I was even able to rsync the
database files direct from the surviving disk over to a backup server
and restart PostgreSQL there straight off, without any evident
problems. (Disclaimer: it was an emergency, and the data was
non-critical; nevertheless I never fou
e LEFT JOIN already contains
> e, c, and ec so it's OK for the JOIN condition to use e. Unfortunately
> for MySQL users everywhere, this is expressly contrary to the SQL spec:
> per spec, JOIN binds more tightly than commas in the FROM-list do.
>
> (Is this on the mysql gotchas
_int := MATCH(txt, '^#(\d+)')::INTEGER;
>
> which would assign the integer atom (\d+) to my_int.
This seems to do what you want:
my_int := (REGEXP_MATCHES(txt, E'^#(\\d+)'))[1];
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
ry turning the fields round, e.g.
WHERE schols_selected LIKE '%' || short_name || '%'
However this is a lousy way of doing things and will cause performance
problems with a non-trivial amount of data. (An additional table is
what would would help you here).
HTH
Ian Barwick
--
-
id | integer | not null default nextval('foo_id_seq'::regclass)
Indexes:
"bar_fkey" PRIMARY KEY, btree (id)
test=#
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
>
> If i read the error message, it means the function has changed or
> something. May I know the latest function so my query can run well in
> my PostgreSQL 8.1.11 ?
Try something like:
select date_part('epoch','2009-04-20 17:08:01'::TIMESTAMP -
'2009-04-20 1
37 matches
Mail list logo
