I've found a number of basic references for PL/PGSQL, but am looking for
something more complete. First question, is there available a *complete*
reference for PL/PGSQL? I'm using PostgreSQL version 7.2.3-5.73 (for
Redhat rpm packaging version), so it is version 7.2 I am concerned with
information on (this is a default version, so at least on this machine,
I'm not interested in updates in version).
The situation that leads me to ask for this is a simple need to select
two fields from a table, and generate a table that itself has two
fields, populated by every permutation of unique values from the first
table field pair. E.G., if I had in table 'one':
left right
==== =====
a b
a c
b d
....then I'd need a list of a, b, c, d, and produce a new table:
left right
==== =====
a b
a c
a d
b a
b c
b d
c a
c b
c d
d a
d b
d c
This isn't hard, but I'm struggling with PL/PGSQL data types. I'd love
to just find a map or associative array type, and populate it from
queries, sort it, and generate a new array or map from a nested loop.
I'm not exactly sure how the PostgreSQL non-standard array type would
work for this from PL/PGSQL, if at all.
D. Stimits, stimits AT comcast DOT net
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings 3 3641
D. Stimits wrote: table field pair. E.G., if I had in table 'one': left right ==== ===== a b a c b d
...then I'd need a list of a, b, c, d, and produce a new table: left right ==== ===== a b a c a d b a b c b d c a c b c d d a d b d c
I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if
possible anyway), but why not:
create table t1(f1 text, f2 text);
insert into t1 values('a','b') ;
insert into t1 values('a','c') ;
insert into t1 values('b','d ');
select a, b
from
(select distinct f1 as a from t1 union select distinct f2 from t1)
as ss1,
(select distinct f1 as b from t1 union select distinct f2 from t1)
as ss2
where ss1.a != ss2.b;
a | b
----+----
a | b
a | c
a | d
b | a
b | c
b | d
c | a
c | b
c | d
d | a
d | b
d | c
(12 rows)
HTH,
Joe
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Joe Conway wrote: D. Stimits wrote:
table field pair. E.G., if I had in table 'one': left right ==== ===== a b a c b d
...then I'd need a list of a, b, c, d, and produce a new table: left right ==== ===== a b a c a d b a b c b d c a c b c d d a d b d c
I don't have 7.2 to test on (and you really should upgrade to 7.3.4 if possible anyway), but why not:
create table t1(f1 text, f2 text); insert into t1 values('a','b') ; insert into t1 values('a','c') ; insert into t1 values('b','d ');
select a, b from (select distinct f1 as a from t1 union select distinct f2 from t1) as ss1, (select distinct f1 as b from t1 union select distinct f2 from t1) as ss2 where ss1.a != ss2.b; a | b ----+---- a | b a | c a | d b | a b | c b | d c | a c | b c | d d | a d | b d | c (12 rows)
This worked quite well, thank you! I'm still in need though of learning
more about PL/PGSQL, as I have other programming to add (well, I could
do this in C as a PostgreSQL C function, but I want to stick to PL/PGSQL
for the moment). I'm still looking for a non-trivial, in-depth, full
reference to PL/PGSQL. I've found many good introductory or tutorial
type web pages, but not a full and complete reference to PL/PGSQL. The
permutations were themselves the easy part, now each permutation has to
do some non-trivial combinatorics on trigger whenever a change is made.
D. Stimits
---------------------------(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
D. Stimits wrote: I'm still in need though of learning more about PL/PGSQL, as I have other programming to add (well, I could do this in C as a PostgreSQL C function, but I want to stick to PL/PGSQL for the moment). I'm still looking for a non-trivial, in-depth, full reference to PL/PGSQL. I've found many good introductory or tutorial type web pages, but not a full and complete reference to PL/PGSQL. The permutations were themselves the easy part, now each permutation has to do some non-trivial combinatorics on trigger whenever a change is made.
I can't point you to anything more than the online docs for PL/pgSQL,
but if you're needing non-trivial statistics/math you should take a look
at using PL/R instead (requires at least Postgres 7.3): http://www.joeconway.com/plr/
HTH,
Joe
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Steve Goldman |
last post by:
Hi,
I am trying to come up with a way to develop all n-length permutations of a
given list of values. The short function below seems to work, but I can't
help thinking there's a better way. Not being a computer scientist, I find
recursive functions to be frightening and unnatural. I'd appreciate if
anyone can tell me the pythonic idiom to accomplish this.
Thanks for your help,
|
by: John Trunek |
last post by:
I have a set of X items, but want permutations of length Y (X > Y). I
am aware of the permutation functions in <algorithm>, but I don't
believe this will do what I want. Is there a way, either through the
STL or some other library to do this, or do I need to write my own
code?
|
by: snpe |
last post by:
Is it down ?
regards
---------------------------(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
|
by: Girish Sahani |
last post by:
Hi guys,
I want to generate all permutations of a string. I've managed to
generate all cyclic permutations. Please help :)
def permute(string):
l=
l.append(string)
string1 = ''
for i in range(0,len(string)-1,1):
string1 = string + string
|
by: anurag |
last post by:
hey can anyone help me in writing a code in c (function) that prints
all permutations of a string.please help
| |
by: Christian Meesters |
last post by:
Hi,
I'd like to hack a function which returns all possible permutations as lists
(or tuples) of two from a given list. So far, I came up with this solution,
but it turned out to be too slow for the given problem, because the list
passed ("atomlist") can be some 1e5 items long:
def permute(atomlist, size = 2):
"""
returns a list of atoms grouped by two
|
by: JosAH |
last post by:
Greetings,
last week we talked a bit about generating permutations and I told you that
this week will be about combinations. Not true; there's a bit more to tell
about permutations and that's what this Tip Of The Week is about. Maybe later
we'll talk a bit about combinations (if anyone's interested).
The little utility class we implemented last week was able to generate a
next permutation (if any) given a current permutation. But what...
|
by: Shraddha |
last post by:
Suppose we are having 3 variables...a,b,c
And we want to print the permutations of these variables...Such
as...abc,acb,bca...all 6 of them...
But we are not supposed to do it mannually...
I want to know that formula by which this can be possible...
Then that program will be ok for nnumber of variables....
Can anyone help me for that?
|
by: Bill Cunningham |
last post by:
I don't know if I'll need pointers for this or not. I wants numbers
10^16. Like a credit card 16 digits of possible 10 numbers, so I guess that
would be 10^16. So I have
int num ;
These are of course declared and not initialized. Now I want to initialize
them all with '\0'. That I'm guessing would involve while ( --). Or maybe
for. Would pointers be involved in this?
With this excercise I would learn working with multi-dimensional
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |