[SQL] Can I create a function that returns many records?
I'd like to create a function that outs the results of a select query.
It might work like this:
SELECT METAPHONE('jennifer foobar');
persid | name
-
1 | jennifer fubar
10 | gennifer foobar
[I already have the metaphone comparing working, it's the returning
the SELECt that needs help.]
Working through the documentation, I see examples using CREATE
FUNCTION METAPHONE(text) RETURNS setof tblPerson AS 'SELECT
* FROM tblPerson' LANGUAGE 'sql', but this returns only a single
numeric value (that doesn't seem to correspond to anything.)
Is there a way to do this? Any help would be very appreciated.
--
Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED]
Support Center of Washington (www.scw.org)
Re: [SQL] Can I create a function that returns many records?
"Joel Burton" <[EMAIL PROTECTED]> writes: > Working through the documentation, I see examples using CREATE > FUNCTION METAPHONE(text) RETURNS setof tblPerson AS 'SELECT > * FROM tblPerson' LANGUAGE 'sql', but this returns only a single > numeric value (that doesn't seem to correspond to anything.) IIRC, you can build a function that returns a SETOF any simple datatype, but the support for returning tuples is suffering from bit-rot. (I imagine it worked, more or less, back in Berkeley days.) If you can live with returning one column at a time, you might be OK. SELECT function-returning-set is a pretty peculiar notation anyway, since SQL doesn't really have any concept of set data types in expressions. What's more likely to be supported someday is a function-returning-set used as a source table in a SELECT's from-clause, ie, SELECT blah blah blah FROM function-returning-set(arguments) ... This doesn't require any creative interpretation of SQL semantics to figure out what to do with the multiple result rows. BTW, is there a good reason why you don't just use a VIEW? regards, tom lane
Re: [SQL] Database authentication and configuration
On Tue, Jul 18, 2000 at 12:01:46PM +1000, Carolyn Lu Wong wrote: > Are there anywhere to configure so that whenever I call 'psql dbname', > it'll always prompt for user authentication instead of getting into the > database directly? Take a look at the configuration of your pg_hba.conf file. You will most likely find that you're set up to trust local users. You can force authentication by changing this to password, crypt, or kerberos based authentication. -- Rodger Donaldson[EMAIL PROTECTED] "Forgive us if we bite your head off; we were led to assume you weren't using it in the first place" --Jim Allenspach, in comp.lang.perl.misc
[SQL] Order by in Select
Hello, I have a list of id's that I need to sort and fetch. eg; 1,3,6,5,4,3,4,5,6,7,5,4,2 Select id >From table Order By id Asc; it becomes like: 1,2,3,3,4,4,4,5,5,5,6,6,7 What can I add to the above sql statement so that is fetches the specified id first in a sorted list? eg: if $specified_id=5; I want: 5,5,5,1,2,3,3,4,4,4,6,6,7 I could have done it in two different sql statements where one fetches specified_id and the other fetches and sorts others, but I want to be able to do it in one sql statement to reduce time. Any ideas? Thank you! - Bernie begin:vcard n:Huang;Bernie tel;fax:(604)664-9195 tel;work:(604)664-9172 x-mozilla-html:TRUE org:Environment Canada;Standards and Technology Services adr:;;700-1200 West 73 Ave.;Vancouver;BC;V6P 6H9;Canada version:2.1 email;internet:[EMAIL PROTECTED] title:Programmer x-mozilla-cpt:;0 fn:Bernie Huang end:vcard
Re: [SQL] Order by in Select
> What can I add to the above sql statement so that is fetches the > specified id first in a sorted list? > > eg: if $specified_id=5; > I want: 5,5,5,1,2,3,3,4,4,4,6,6,7 > > I could have done it in two different sql statements where one fetches > specified_id and the other fetches and sorts others, but I want to be > able to do it in one sql statement to reduce time. Umm, maybe something like: select id from table order by case when id=$specified_id then else id end
[SQL] synchronizing databases
Hi. Again, I'm posting to this list because none of the other postgres lists seem quite right for the topic. Excuse me if I'm off topic. I'm writing an application and I'd like to have the data that application uses to be uninterrupted in case the database server fails (fail over). Likewise, when I'm ready to bring the crashed server back online I'll need to be able to synchronize it with the one that's been working so that I can go back to having fail over capability. I know that oracle has a fail safe DB server, MsSQL has some form of replication, and I can do it on the hardware level with a mirrored drive array. I'd like to avoid shelling out the cash for these solutions and was exploring the potential for an application/database layer solution. I'd also gladly accept open source solutions like linux software raid or clustering from anyone who's used them in this context! I've thought of a couple possible solutions. I'm sure that this problem has been tackled before and I'd appreciate feedback from anyone with experience. In the first scenario, I make the update, and also write a log entry that will allow me to re-create that update. I do this as a transaction first on server x, then on server y. The log entry has a key field that allows me to check server x's log against that of server y, and if discrepancies exist, I can bring the server that is missing the transaction up to date with a synchronization program that runs periodically. There are problems with this. I have to return success after x has been updated even if the update to y fails, because the update HAS happened, and under normal conditions y will get synchronized. But if x crashes immediately afterwards, then y will never show that the update happened, but the outside world will have seen it as a success. A more complex solution is to open the transaction on x then update the record in y with a flag that shows that an update MIGHT have occurred. If that update to y succeeds, then I can commit the transaction on x (at this point I must return success to the outside world) and start another transaction on y. This time I synchronize y with x and remove the flag in one transaction. If x crashes at any time, transactions are rejected until the system fails over to y. If y hiccups during a transaction on x so that y doesn't get flagged, then I return failure and don't commit to x. If my application crashes before synchronizing y and x fails right after that, then I have to freeze all records in y that have flags until I can get their real status back from x (if that's recoverable) or I'm left with orphans that I'll have to resolve using real world info. Meanwhile I have scripts that can create a new y from an x, which I use in case y goes down or in case x goes down and y has to BECOME x. Is there a more elegant solution to this? Thanks in advance! --->Nathan -- ¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ø,¸¸,ø¤º°`°º¤ Nathan Young [EMAIL PROTECTED] (805) 686-2830
[SQL] from not null field to nullable field?
I have a field in a table that has been defined 'not null'. Is it possible to remove this constraint? I don't see this option under 'alter table'.
