Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Ok, to do anything with your result set in <code>print_result</code> you'll have to loop over it. That'll look something like this - </p> <p>Here <code>result_record</code> is defined as a <code>record</code> variable. For the sake of explanation, we'll also assume that you have a <code>formatted_results</code> variable that is defined as <code>text</code> and defaulted to a blank string to hold the formatted results.</p> <pre><code>FOR result_record IN SELECT * FROM query_result(this_query) AS t (id int) LOOP -- With all this, you can do something like this formatted_results := formatted_results ||','|| result_record.id; END LOOP; RETURN formatted_results; </code></pre> <p>So, if you change <code>print_results</code> to return text, declare the variables as I've described and add this in, your function will return a comma-separated list of all your results (with an extra comma at the end, I'm sure you can make use of PostgreSQL's <a href="http://www.postgresql.org/docs/8.4/static/functions-string.html" rel="nofollow">string functions</a> to trim that). I'm not sure this is exactly what you want, but this should give you a good idea about how to manipulate your result set. You can get more information <a href="http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html" rel="nofollow">here</a> about control structures, which should let you do pretty much whatever you want. </p> <p>EDIT TO ANSWER THE REAL QUESTION:</p> <p>The ability to format data tuples as readable text is a feature of the psql client, not the PostgreSQL server. To make this feature available in the server would require extracting relevant code or modules from the psql utility and recompiling them as a database function. This seems possible (and it is also possible that someone has already done this), but I am not familiar enough with the process to provide a good description of how to do that. Most likely, the best solution for formatting query results as text will be to make use of PostgreSQL's string formatting functions to implement the features you need for your application.</p>
    singulars
    1. This table or related slice is empty.
    1. POPrint ASCII-art formatted SETOF records from inside a PL/pgSQL function
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USScott S
    1. USScott S
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThank you @Scott: I knew I could manually format the textual output, but I rather wanted to understand if there is a way to exploit the standard sort-of-ASCII-art formatting SQL function that is used to beautify the results from terminal calls.
      singulars
      1. PO
      1. USCampa
    2. COAh, ok, I think I understand what you want now. The postgres server does not do that formatting, it just returns the data. The "psql" client you are using formats the data it gets. You would have to grab the source for the psql utility and find the code in there that does that, and then compile it as a function for the postgres server. Doable, I'm sure, but I couldn't tell you the exact steps. It is probably easier to implement something with postgres's string functions to meet your needs.
      singulars
      1. PO
      1. USScott S
    3. COI see.. thank you Scott. PS You might update your answer with this detail at some point! I believe I'll sort it out by decomposing my function to smaller PL/pgSQL functions returning `SETOF` records, so that I don't have to cope with home-brewed beautifyings.
      singulars
      1. PO
      1. USCampa
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload