Markdown in Table

[rklump]

New user to SQLPage and have a small application that is working well and very happy with it. I have run into an issue with trying to enhance a table column with a markdown link. After many attempts, decided to just create a basic page and I have replicated my problem.

SELECT 'table'  AS component,
       'Links:' AS title,
       'Link'   AS markdown,
       TRUE AS sort,
       TRUE AS search;

SELECT description AS Description,
       '[' || url || '](' || url || ')' AS Link
FROM myurls;

SELECT 'desc' as Description,
       '[desc](desc)' as Link;

The hard coded entry at the bottom of the code displays the desired link and text. Selecting from my sample database table displays the desired markup as text.

I must be missing something very simple. I'm using SQLPage v0.28

[lovasoa]

Hi! if the link text is the url itself, then you don't need any special syntax. URLs are automatically made clickable when rendering markdown:

SELECT 'table'  AS component,
       'Links:' AS title,
       'Link'   AS markdown,
       TRUE AS sort,
       TRUE AS search;

SELECT description AS Description, url AS Link
FROM my_urls

[rklump]

Thank you for your reply, but I am unable to get this simple example to work.

The myurls table (postgers db) is defined as:
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------+-------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
mstsid | integer | | not null | nextval('myurls_mstsid_seq'::regclass) | plain | |
description | character varying | | | | extended | |
url | character varying | | | | extended | |
Indexes:
"myurls_pkey" PRIMARY KEY, btree (mstsid)
Access method: heap

The code for the page is exactly as you wrote. The rendered page does not show the Link column as a link -- it is rendered as plain text. The values of description are "myurl" and the url value is "http:/servername:8080/testusers2.sql".

[lovasoa]

Looks like there is a single slash instead of two after "http"

[rklump]

Thank you -- typo corrected but no change in display. The link does not show a link.

[lovasoa]

Hello !
I cannot reproduce this:

[YunusMechery]

@rklump I had the same issue. Finally url_encode worked for me.

SELECT description AS Description,
       CONCAT(
		  '['
		, url
		, ']('
		, sqlpage.url_encode(url)
		, ')'
	     )  AS Link
FROM myurls;

[lovasoa]

This behavior is not normal. Could you please make a self contained example that I could easily reproduce?

[rklump]

Thank you for your assistance. I'm getting the feeling that my issue is not easily reproduceable.

I've included:
db.dump.sql containing my table definitions and values -- Postgres 13.16
testusers2.sql, testusers2a.sql, and testusers2b.sql -- containing various pages I've been testing with

Using SQLPage V.28 on a RHEL 9.4 system.

Please let me know what else I can provide.
sqlpage.markdown.600.tar.gz

[lovasoa]

@rklump : thank you ! I can reproduce it. I'll look into it as soon as I get the time !

This clearly sounds like a bug in SQLPage

[lovasoa]

@rklump : I found where the problem comes from.

The reason

PostgreSQL folds all names [...] to lower case unless they're "double quoted".

So, when you execute SELECT 'x' as Link, postgres returns { "link": "x" } (lowercase) instead of { "Link": "x" } (capitalized)

Reference:

• https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
• https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_upper_case_table_or_column_names

The fix

Use double quotes around Link in as Link:

SELECT description AS Description,
       $thislinkofmine as "Link"
FROM myurls where description='myurl';

SQLPage improvements ?

This is difficult to fix directly inside sqlpage, since it's postgres that changes the case of the identifier.

One thing that we could do is match column names case-insensitively when looking for markdown columns, so that 'Link' as markdown would match against a column named link.

[rklump]

Thank you, so much, for identifying the upper case/lower case folding issue. While all of my table/column names are in lower case in the DB, I did not realize that this rule would extend to the display column names created through SQLPage. I'll be sure to use the double quotes when I want to display with capital letters.

I'm not sure if making a change in SQLPage is warranted for this issue. Maybe it would be beneficial for users new to SQLPage using Postgres -- but that may be a small segment of your user base.

[lovasoa]

I made the change. This will avoid people bumping on this too.
The old behavior was too surprising. Using markdown in tables is a common use case.